Solved

Excel Help

Posted on 2013-01-23
6
323 Views
Last Modified: 2013-01-23
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
Comment
Question by:elwayisgod
  • 3
  • 3
6 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 38810507
Is there only one value in column C to use?

If not, then when do you go to next value in C?
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38810524
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
 

Author Comment

by:elwayisgod
ID: 38810585
No the value changes in Column C.
0
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!

 

Author Comment

by:elwayisgod
ID: 38810610
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
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 38810640
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
 

Author Closing Comment

by:elwayisgod
ID: 38811097
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question