Quantcast

excel gurus

jonKranked

Detective Dookie
Nov 10, 2005
85,562
24,182
media blackout
need a quick help

i have a table, need to consolidate data. 2 columns. first column contains a unique identifier (but with many repeats). second column is subcomponents from a BOM (the unique identifier for each BOM is in the first column). i need a quick way to consolidate the data so each unique identifier from the first row is is only listed once per row, and all the corresponding subcomponents in the second column get propagated as new columns. copy --> paste transpose will take forever, (30k+ rows of data). is there a quick way to do this? screen caps below should help illustrate what i need to do

before:
upload_2018-12-10_14-38-32.png


after:

upload_2018-12-10_14-38-44.png
 

Jm_

sled dog's bollocks
Jan 14, 2002
18,852
9,557
AK
You might be able to filter each one, make separate spreadsheets of those filters if there aren't too many repeats, total them in the separate spreadsheet, then go back and create summary spreadsheet?
 

jonKranked

Detective Dookie
Nov 10, 2005
85,562
24,182
media blackout
You might be able to filter each one, make separate spreadsheets of those filters if there aren't too many repeats, total them in the separate spreadsheet, then go back and create summary spreadsheet?
there's over 30k rows, and like 5k unique BOM #'s. trying to automate it
 

Jm_

sled dog's bollocks
Jan 14, 2002
18,852
9,557
AK
there's over 30k rows, and like 5k unique BOM #'s. trying to automate it
Well then you're F-ed.

Usually my data of the same size only has one identifier that I'm looking for, so I'm just trying to filter out every line that isn't that identifier. I get it in a ridiculous text file though and then have to convert to CSV or excel and it's major PITA.

It kind of sounds like that data is better sorted through a database, although there is probably some excel wizardry that can do it. Probably a complex calculation that ties to the identifying information. I'd really need to see the data and play with it. I do these things on occasion, but not with enough regularity to be any kind of excel expert. Every time it takes me hours just to figure out how to go about it.
 

KenW449

Thanos did nothing wrong
Jun 13, 2017
2,704
329
Floating down the whiskey river...
May not be the quickest way, but it's easy to explain. And i don't know any other automated way
First you will want to filter columns A and B
Second you will want to make a second sheet
3: Filter out everything but number 1
4: Copy everything from column B, note click the first box in row 1, column B then scroll down to the last filled box in whatever row and "shift click" it. So if your last row is 4, shift click row 4 in column b and it will highlight all boxes in between.
5: Go to sheet 2, and type 1 in row 1, column A.
6: in row 1, column b, click the little arrow under paste and there is an option to switch rows and columns. click that and it will paste how you want. Give me a couple minutes and i will have pics.
 

jonKranked

Detective Dookie
Nov 10, 2005
85,562
24,182
media blackout
Well then you're F-ed.

Usually my data of the same size only has one identifier that I'm looking for, so I'm just trying to filter out every line that isn't that identifier. I get it in a ridiculous text file though and then have to convert to CSV or excel and it's major PITA.

It kind of sounds like that data is better sorted through a database, although there is probably some excel wizardry that can do it. Probably a complex calculation that ties to the identifying information. I'd really need to see the data and play with it. I do these things on occasion, but not with enough regularity to be any kind of excel expert. Every time it takes me hours just to figure out how to go about it.
The data came from our erp system. Which is fucking ancient. I'm talking the front end we access it through is a fucking telnet. We're prepping for a data migration to SAP.
 

HOOWAH

Monkey
Sep 16, 2001
105
0
portland, maine USA
Figure it out yet?
Build an empty shell of what you want it to be in a new sheet with row headings being unique ids and column headings being the unique 'boms'. Then use a vlookup function to fill the values in the matrix by assembling the value you want to look up from the row and column headings and then finding it in your original list.

Or, it can be done in about 2 minutes using R tidyverse package using spread function if you know R.