Consolidation of Data

Guys I have a table of data that has data like


7082*1
7082*2
6583*1
6480*2

So I want a consolidation of different columns to get a final result of

SKU QTY
7082 3
6583 1
6480 2

*1 is quantity of 1
*2 is quantity of 2

I have attached an excel spreadsheet for illustration

Originally Posted by Sambo kid

what do columns B and C have to do with the results? AND, you have a result of 362 for 7082 and 20 for 6583 but by my best quick count I count 19 for 7082 and 2 for 6583.

AND, are all the numbers you are looking for on the left side of * four digit?

Hi they may not be but the number after the * is the quantity

They are actually product sku for all 3 columns derived from different product options choices

So I need to sum them up according to the sku to deduct from my inventory system

Hope I am clear

The number I entered into the excel sheet is just arbitrary as an example

Originally Posted by CK76

Try something like below in F4...

Formula:

Please Login or Register  to view this content.

Confirmed as array (CTRL + SHIFT + ENTER)
Copy down.

Attachment 620531



Edit: Though I'd personally recommend using Text to Columns or using formula to separate out left and right of "*".

Thanks cause they are from 3 columns and I want to consolidate all 3 columns sku and split them up into sku and quantity into 1 single column

Hmm? Did the formula not work?

If you need to search across all 3 columns... easiest method is just to do addition.

But as I mentioned, data prep/transformation is recommended here. In my opinion, that's about 85% of data analyst/scientist's work.