2

Could anyone help me write an Excel formula to do the following? I am trying to merge product codes for a customer from a flat file extract. Each customer can have multiple products, and vice versa (this is a many:many relationship). What I need is to generate a separate sheet with a simple list of customers and associated products in comma-separated manner. See image to understand what I am trying to do:

http://i.stack.imgur.com/QJgsK.jpg

1
  • Silly suggestion, but this looks remarkably like the kind of thing you'd use a database for. Have you considered using Access or similar? If I recall right, Access can even directly import Excel sheets.
    – Karu
    Mar 21 '16 at 9:01
1

This is much easier if you’re willing to create a couple “helper columns”.  It’s simplest if you put them on Sheet 1 (you can hide them, or put them way off to the right (e.g., Columns Y and Z), if you want — or you can even put them on another sheet.  I’ll assume that we’re using Columns Y and Z as helper columns, “Cust ID” and “Product ID” are in Columns A and B, respectively, and the data start in Row 2 (with headers in Row 1).

Enter the following value and formulas:

  • Y20
  • Y3=IF(A3=A4, Y2, Y2+1)
  • Z2=IF(A1=A2, Z1 & ", " & B2, B2)

Drag/fill Y3 and Z2 down as far as the data go.  You should get something like this:

Column Y identifies the range of rows for a single customer.  If the next row has a different customer ID than this one (i.e., if this is the last row for this customer), we add 1 to the previous Y value (i.e., the row above).  Otherwise, copy the previous value.

Column Z builds the list of products for each customer.  If the previous row has the same customer number as this one, that means that the current row in not the first row for this customer, so we take the value of Z from the previous row and append the product from this row.  Otherwise, this is the first row for this customer, so we simply take the product from this row.  (The previous two sentences might make more sense if you read them in the reverse order.)

Now, go to Sheet2.  Same as before; I’ll assume that “Cust ID” and “Products” are in Columns A and B, respectively, and the data start in Row 2 (with headers in Row 1).  Enter these formulas:

  • A2=INDEX(Sheet1!A:A, MATCH(ROW()-1, Sheet1!Y:Y, 0))
  • B2=INDEX(Sheet1!Z:Z, MATCH(ROW()-1, Sheet1!Y:Y, 0))

and drag/fill down.

Notice that the two formulas have an expression in common: MATCH(ROW()-1, Sheet1!Y:Y, 0).  That finds the first row on Sheet1 where the Y value is the current (Sheet2) row number, minus 1.  Thus, Sheet2 Row 2 looks for a 1, Sheet2 Row 3 looks for a 2, etc.  Remember, the Sheet1!Y values go up by one on the last row for a customer.  So the formula in Column A pulls the Sheet1!A value for that row, which is the next unique customer ID, and the formula in Column B pulls the Sheet1!Z value for that row, which is the list of products for that customer:

            

0
0

It took some tweaking but after a bit of trial and error this worked on a merge of two identical spreadsheets containing "some" different data. Each at over 15,000 lines and 132 columns! Random spot checks are all good! This saved me a ton of time, Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.