Mutiple Values per each Single ID - Need a horizontal list

Hi,

I have a large list, almost 2200 lines, of 4 letter company codes in column A with account numbers in column B. Each company can anywhere from 1 to 10 account numbers. Therefore, sometimes column A will have 1 company code with column B having 1 corresponding account number - or - column A could have the same company code 10 times with the corresponding account numbers in column B. I need to turn this into a a list containing the company code in column A and the corresponding account numbers in columns B,C,D,E etc. (Also could have all acount numbers in one cell in column B separated by spaces - that would work too). I have attached the file. Any ideas?

Try the following,

In C2 add a formula:

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,A2&"_"&COUNTIF(C$1:C1,A2&"*")+1,"")

copied down.

then select Column A and go to Data and select Advanced from the Sort & Filter section.

Select Copy to Another Location,

Ensure List range is your column A range.

Leave the Criteria range blank

in the Copy to range, enter or select first cell of column to copy to... e.g. $F$1

Check the Unique Records Only checkbox

Then in G2 enter formula:

=IFERROR(INDEX($B:$B,MATCH($F2&"_"&COLUMNS($A$1:A$1),$C:$C,0)),"")

copy across 10 columns to cover all results and then copy down the list of unique items.

tombo222,

Welcome to the Excel Forum.


Detach/open workbook ReorgData w1 A B wR AdvancedFilter Unique A B C plus - tombo222 - EF775326 - SDG13.xlsm and run macro ReorgData.

The macro will create a new worksheet Results containing a unique list of SCAC codes in column A, and column B will contain the TRAC_ACCOUNT_NUMBERS with two spaces between each number if there are two or more, and columns C thru ? will contain all the numbers in their owrn column.


To run the macro on another workbook, with your raw data in worksheet scac+COMPANY_TRAC_ACCOUNT:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.