Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Excel Help

I have a large 5 Column 5 (A to E).  This file is 518K rows.  Column D is Blank.  What I need is a formula that would take the value in Column C and append 'Group 1' to it for first 1000 rows.  Then append 'Group 2' for next 1000 rows etc... So if Column C was 'Test' then Column D would be 'Test Group 1' for first 1000 Rows and 'Test Group 2 for next 1000 rows.  I don't want to manually do a concatenate. Somehow I need the logic to go by the row number.

I could also convert this to a TAB delimited text file and if a VB script could do it easier that's fine too.  Just need 4th column to contain the result.
0
elwayisgod
Asked:
elwayisgod
  • 3
  • 3
1 Solution
 
NBVCCommented:
Is there only one value in column C to use?

If not, then when do you go to next value in C?
0
 
NBVCCommented:
If you are saying that C2 has "TEST" and C3 has "OTHER", then you want 1000 TEST Group 1 and 1000 Other Group 2, etc.. then try.

=INDEX(C:C,MOD(INT((ROW()-ROW($C$1))/1000),1000)+2)&" GROUP "&MOD(INT((ROW()-ROW($C$1))/1000),1000)+1

copied down.

Otherwise, please elaborate.
0
 
elwayisgodAuthor Commented:
No the value changes in Column C.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
elwayisgodAuthor Commented:
NB_VC,

Not what I'm saying.   To clarify.  Column C has two text values in it.  It has 'Test' for first 458,250 Rows.  It has 'Test2' in it for remaining Rows 458,251 to 518,375.  

Column D needs to break these into groups of 1000 by appending 'Group 1' for first 1000 rows, Group 2 for next 1000 rows etc......

That help?
0
 
NBVCCommented:
So then assuming first text in C1, try:

=C1&" GROUP"&MOD(INT((ROW()-ROW($C$1))/1000),1000)+1

copied down

Is that it?
0
 
elwayisgodAuthor Commented:
Perfect!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now