Having to restructure table to a strange format, need ideas on how to do it in bulk

[bstaaa]

I have a set of data as shown in the table below that I need to showcase in the format as shown in the 2nd table.

But a little background first: In the table below we used a typical format for customer data, where each unique combination of data had its own row, so if Customer 12345 had US locations and ordered 2 different products, we would showcase this information over 2 lines. And if Customer 12346 only bought 1 product, but for 4 different locations it would consist of 4 separate lines. See the 1st table for an example.

How the data is currently displayed:

Customer	Location(s)	Product	Sales Rep
12345	United States	412	John
12345	United States	413	John
12346	Germany	416	Matt
12346	United States	416	Matt
12346	Poland	416	Matt
12346	Mexico	416	Matt
12348	Mexico	413	Dan
12348	Germany	413	Dan
12348	Mexico	689	Dan
12348	Germany	689	Dan

<tbody>
</tbody>

But due to a pending system move we need to alter our customer data into an alternative structure to be uploaded to the new system, and it needs to be in the format shown in the table below. I'm at a loss at how to restructure this via VBA as the new format is so bizarre in my opinion. My only thought is to separate the data into 3 tables (customer & location, customer & product, customer & sales rep) then insert blank rows manually for the number of locations and product for each unique customer/sales combo.

How I need the data to look:

Customer	Location	Product	Sales Rep
12345	United States	412	John
		413
12346	Germany	416	Matt
	United States
	Poland
	Mexico
12348	Mexico	413	Dan
	Germany
		689

<tbody>
</tbody>

Any thoughts on how this could be done via VBA or in a more automated manner? Happy to answer any questions.

 

[Gerald Higgins]

I'm guessing you might have made a mistake on the last line of table 2, and "689" should actually be on the same line as "Germany". Correct ?

If so, I think you might be able to do it with some simple helper columns.

Let's assume your data in table 1 is in the range A1:D11, with headers in row 1.

In E2, put this . . .
=IF(A2=A1,"",A2)

In F2, put this . . .
=IF(AND(A2=A1,B2=B1),"",B2)

In G2, put this . . .
=IF(AND(A2=A1,C2=C1),"",C2)

I'll leave you to work out what to put in H2.

Then copy these formulas all the way down, and you should end up with table 2.

 

[bstaaa]

I'm guessing you might have made a mistake on the last line of table 2, and "689" should actually be on the same line as "Germany". Correct ?

If so, I think you might be able to do it with some simple helper columns.

Let's assume your data in table 1 is in the range A1:D11, with headers in row 1.

In E2, put this . . .
=IF(A2=A1,"",A2)

In F2, put this . . .
=IF(AND(A2=A1,B2=B1),"",B2)

In G2, put this . . .
=IF(AND(A2=A1,C2=C1),"",C2)

I'll leave you to work out what to put in H2.

Then copy these formulas all the way down, and you should end up with table 2.

Strangely enough it is supposed to be on a separate line. Each new unique location/product for a customer after the initial row is put on a separate row by iteslf. For example, should that last customer 12348 have purchased product 416 as well, we would add another row beneath 689 to display this additional unique item.

 

[Gerald Higgins]

Hi, in my opinion there is usually no need to quote other posts in full

Your comment about the last line - really ? OK, well we can probably replicate that.

But let's park that for a moment.

Does my suggested solution work for all the other lines ?

 

[bstaaa]

Yes! The format works great for all the core lines.

And yeah that last line is wonky, it really is the stupidest format ever.

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub RestructureTable()
  Dim LastRow As Long, Col As Long, Ar As Range
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  Application.ScreenUpdating = False
  Range("A3:A" & LastRow) = Evaluate("IF(A3:A" & LastRow + 1 & "=A2:A" & LastRow & ","""",A3:A" & LastRow & ")")
  For Col = 2 To 4
    For Each Ar In Range("A1:A" & LastRow).SpecialCells(xlBlanks).Areas
      Ar.Offset(-1, Col - 1).Resize(Ar.Count + 1).RemoveDuplicates Columns:=1, Header:=xlNo
    Next
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]