Previous Topic Next Topic

Excel 2010 Series question

[Closed]
Report
-
 cds_arabians -
Hello,

I have a question. I have an excel spreadsheet with 25 columns of information. There are anywhere from 3500 to 4000 rows of information. The 1st column contains a business unit: PS005, PS006, PS020, etc. These are not sequential. The 3rd column contains an invoice number: 0168316IN. In this case, the 3500 to 4000 rows all contain the same invoice number. Here is my dilemma. Our accounting department needs a unique invoice number for each 500 lines of the same business unit or if a change of business unit occurs. When we change the invoice number, we basically add an "A", "B", "C", etc. after the original invoice number, so in this case, it would be 0168316INA. My dilemma is this is very time consuming to manually do this. Is there any way to run a macro or code to do this for me once I have the document completed. Is there some way to automatically add the "A", "B", "C", etc. for me? I haven't been able to figure out a way so I am asking for help.

Thanks for all the input!

1 reply

Answer 1 / 1

Yes, create a column that has A, B, C in the first three rows. Then select those three and copy till 4000 rows. This will generate unique A,B,C etc. for you. Then create another column and in that column write a forumla that concatenates the invoice column and the new column with A,B,C etc. This way you have it.
Report

That would work if I had the same amount of lines for each A, B, C, etc. Let's say that of the 4000 lines, the first 2389 lines need to be split into A, B, C, D, and E because I can only have 500 lines per unique invoice number. Lines 2390 to 2393 would need to be F because the Business Unit changed. Then from 2394 to 2500 would need to be G because the Business Unit changed again. The number of lines can change per report. I need the formula to first look at the Business Unit and count down 500 lines and if the Business Unit is the same, then change the Invoice number to have the next incremental alphabet letter after it. If it is not the same, I need it to go back to the when the Business Unit changed and change the invoice number there. Then I need it to look for the next Business Unit change as long as it is not more than 500 lines down and change the invoice number again. Each group of Business Units can change in the amount of lines it may be. I never know how many is in each group before I run the report. Is this possible to do? Thanks for your help!
Ask a question