convert multiple rows to one row per person with additional columns

[dave501]

Hi,

I have a spreadsheet with multiple records per person, I would like to convert into one row per person. I will give an example below.

Columns A to D will be the same in every row for every person. The order details in the remaining columns (E to H) will be different.

[TABLE="width: 1178"]
<tbody>[TR]
[TD]Person Unique ID[/TD]
[TD]Name[/TD]
[TD]Surname[/TD]
[TD]Address[/TD]
[TD]Order id[/TD]
[TD]Order description[/TD]
[TD]order date[/TD]
[TD]order price[/TD]
[/TR]
[TR]
[TD]A44[/TD]
[TD]Alex[/TD]
[TD]Smith[/TD]
[TD]1 High Street[/TD]
[TD]5[/TD]
[TD]apples[/TD]
[TD]01/01/2000[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D516[/TD]
[TD]Diane[/TD]
[TD]Jones[/TD]
[TD]222 High Street[/TD]
[TD]4[/TD]
[TD]bananas[/TD]
[TD]01/02/2000[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]D516[/TD]
[TD]Diane[/TD]
[TD]Jones[/TD]
[TD]222 High Street[/TD]
[TD]34[/TD]
[TD]grapes[/TD]
[TD]03/04/2010[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]D516[/TD]
[TD]Diane[/TD]
[TD]Jones[/TD]
[TD]222 High Street[/TD]
[TD]56[/TD]
[TD]peas[/TD]
[TD]05/06/2012[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]D700[/TD]
[TD]David[/TD]
[TD]Green[/TD]
[TD]55 High Road[/TD]
[TD]567[/TD]
[TD]peppers[/TD]
[TD]03/04/2015[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]D700[/TD]
[TD]David[/TD]
[TD]Green[/TD]
[TD]55 High Road[/TD]
[TD]1234[/TD]
[TD]lettuce[/TD]
[TD]03/05/2013[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]D700[/TD]
[TD]David[/TD]
[TD]Green[/TD]
[TD]55 High Road[/TD]
[TD]2123[/TD]
[TD]olives[/TD]
[TD]07/08/2009[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]E55[/TD]
[TD]Eric[/TD]
[TD]Martin[/TD]
[TD]100 High Road[/TD]
[TD]654[/TD]
[TD]strawberries[/TD]
[TD]06/07/2015[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]E55[/TD]
[TD]Eric[/TD]
[TD]Martin[/TD]
[TD]100 High Road[/TD]
[TD]89[/TD]
[TD]potatoes[/TD]
[TD]01/01/2014[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]F223[/TD]
[TD]Fiona[/TD]
[TD]Smith[/TD]
[TD]25 Church Street[/TD]
[TD]98[/TD]
[TD]salt[/TD]
[TD]02/01/2008[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]F223[/TD]
[TD]Fiona[/TD]
[TD]Smith[/TD]
[TD]25 Church Street[/TD]
[TD]766[/TD]
[TD]sugar[/TD]
[TD]10/06/2015[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]F223[/TD]
[TD]Fiona[/TD]
[TD]Smith[/TD]
[TD]25 Church Street[/TD]
[TD]4544[/TD]
[TD]jam[/TD]
[TD]09/06/2015[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]G55[/TD]
[TD]Gary[/TD]
[TD]King[/TD]
[TD]100 Oxford Road[/TD]
[TD]65[/TD]
[TD]marmalade[/TD]
[TD]08/06/2015[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


Spreadsheet will be sorted by column A - person unique ID so all a persons records will be consecutive in spreadsheet. A person will have a maximum of 3 lines on a spreadsheet. The outcome I would like to get is like the example below.

[TABLE="width: 2064"]
<tbody>[TR]
[TD="class: xl65, width: 129"]Person Unique ID[/TD]
[TD="class: xl65, width: 129"]Name[/TD]
[TD="class: xl65, width: 129"]Surname[/TD]
[TD="class: xl65, width: 129"]Address[/TD]
[TD="class: xl65, width: 129"]Order 1 id[/TD]
[TD="class: xl65, width: 129"]Order 1 description[/TD]
[TD="class: xl65, width: 129"]order 1 date[/TD]
[TD="class: xl65, width: 129"]order 1 price[/TD]
[TD="class: xl65, width: 129"]Order 2 id[/TD]
[TD="class: xl65, width: 129"]Order 2 description[/TD]
[TD="class: xl65, width: 129"]order 2 date[/TD]
[TD="class: xl65, width: 129"]order 2 price[/TD]
[TD="class: xl65, width: 129"]Order 3 id[/TD]
[TD="class: xl65, width: 129"]Order 3 description[/TD]
[TD="class: xl65, width: 129"]order 3 date[/TD]
[TD="class: xl65, width: 129"]order 3 price[/TD]
[/TR]
[TR]
[TD="class: xl66"]A44[/TD]
[TD="class: xl66"]Alex[/TD]
[TD="class: xl66"]Smith[/TD]
[TD="class: xl66"]1 High Street[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]apples[/TD]
[TD="class: xl67"]01/01/2000[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]D516[/TD]
[TD="class: xl66"]Diane[/TD]
[TD="class: xl66"]Jones[/TD]
[TD="class: xl66"]222 High Street[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]bananas[/TD]
[TD="class: xl67"]01/02/2000[/TD]
[TD="class: xl66"]45[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]grapes[/TD]
[TD="class: xl67"]03/04/2010[/TD]
[TD="class: xl66"]32[/TD]
[TD="class: xl66"]56[/TD]
[TD="class: xl66"]peas[/TD]
[TD="class: xl67"]05/06/2012[/TD]
[TD="class: xl66"]200[/TD]
[/TR]
[TR]
[TD="class: xl66"]D700[/TD]
[TD="class: xl66"]David[/TD]
[TD="class: xl66"]Green[/TD]
[TD="class: xl66"]55 High Road[/TD]
[TD="class: xl66"]567[/TD]
[TD="class: xl66"]peppers[/TD]
[TD="class: xl67"]03/04/2015[/TD]
[TD="class: xl66"]5000[/TD]
[TD="class: xl66"]1234[/TD]
[TD="class: xl66"]lettuce[/TD]
[TD="class: xl67"]03/05/2013[/TD]
[TD="class: xl66"]54[/TD]
[TD="class: xl66"]2123[/TD]
[TD="class: xl66"]olives[/TD]
[TD="class: xl67"]07/08/2009[/TD]
[TD="class: xl66"]10[/TD]
[/TR]
[TR]
[TD="class: xl66"]E55[/TD]
[TD="class: xl66"]Eric[/TD]
[TD="class: xl66"]Martin[/TD]
[TD="class: xl66"]100 High Road[/TD]
[TD="class: xl66"]654[/TD]
[TD="class: xl66"]strawberries[/TD]
[TD="class: xl67"]06/07/2015[/TD]
[TD="class: xl66"]20[/TD]
[TD="class: xl66"]89[/TD]
[TD="class: xl66"]potatoes[/TD]
[TD="class: xl67"]01/01/2014[/TD]
[TD="class: xl66"]30[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]F223[/TD]
[TD="class: xl66"]Fiona[/TD]
[TD="class: xl66"]Smith[/TD]
[TD="class: xl66"]25 Church Street[/TD]
[TD="class: xl66"]98[/TD]
[TD="class: xl66"]salt[/TD]
[TD="class: xl67"]02/01/2008[/TD]
[TD="class: xl66"]44[/TD]
[TD="class: xl66"]766[/TD]
[TD="class: xl66"]sugar[/TD]
[TD="class: xl67"]10/06/2015[/TD]
[TD="class: xl66"]100[/TD]
[TD="class: xl66"]4544[/TD]
[TD="class: xl66"]jam[/TD]
[TD="class: xl67"]09/06/2015[/TD]
[TD="class: xl66"]10[/TD]
[/TR]
[TR]
[TD="class: xl66"]G55[/TD]
[TD="class: xl66"]Gary[/TD]
[TD="class: xl66"]King[/TD]
[TD="class: xl66"]100 Oxford Road[/TD]
[TD="class: xl66"]65[/TD]
[TD="class: xl66"]marmalade[/TD]
[TD="class: xl67"]08/06/2015[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]

Does anyone have any advice on how to achieve this?

Thanks,

 

[Zakkaroo]

Assuming your original data is in Columns A to H:

If it was me, in your original data, I would add a column to the left of the Customer ID and in that column put the formula:

=B2&"_"&COUNTIF($B$2:B2,B2)

And then fill it down. in your specific example of data, it would end up looking like this

Excel 2010

	A	B

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: center"]Unique Identifier
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Person Unique ID
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: center"]A44_1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]A44
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: center"]D516_1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]D516
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: center"]D516_2
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]D516
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: center"]D516_3
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]D516
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: center"]D700_1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]D700
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: center"]D700_2
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]D700
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: center"]D700_3
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]D700
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: center"]E55_1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]E55
[/TD]

[TD="align: center"]10
[/TD]
[TD="align: center"]E55_2
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]E55
[/TD]

[TD="align: center"]11
[/TD]
[TD="align: center"]F223_1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]F223
[/TD]

[TD="align: center"]12
[/TD]
[TD="align: center"]F223_2
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]F223
[/TD]

[TD="align: center"]13
[/TD]
[TD="align: center"]F223_3
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]F223
[/TD]

[TD="align: center"]14
[/TD]
[TD="align: center"]G55_1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]G55
[/TD]

</tbody>

Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A2
[/TH]
[TD="align: left"]=B2&"_"&COUNTIF($B$2:B2,B2)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A3
[/TH]
[TD="align: left"]=B3&"_"&COUNTIF($B$2:B3,B3)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A4
[/TH]
[TD="align: left"]=B4&"_"&COUNTIF($B$2:B4,B4)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A5
[/TH]
[TD="align: left"]=B5&"_"&COUNTIF($B$2:B5,B5)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A6
[/TH]
[TD="align: left"]=B6&"_"&COUNTIF($B$2:B6,B6)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A7
[/TH]
[TD="align: left"]=B7&"_"&COUNTIF($B$2:B7,B7)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A8
[/TH]
[TD="align: left"]=B8&"_"&COUNTIF($B$2:B8,B8)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A9
[/TH]
[TD="align: left"]=B9&"_"&COUNTIF($B$2:B9,B9)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A10
[/TH]
[TD="align: left"]=B10&"_"&COUNTIF($B$2:B10,B10)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A11
[/TH]
[TD="align: left"]=B11&"_"&COUNTIF($B$2:B11,B11)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A12
[/TH]
[TD="align: left"]=B12&"_"&COUNTIF($B$2:B12,B12)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A13
[/TH]
[TD="align: left"]=B13&"_"&COUNTIF($B$2:B13,B13)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A14
[/TH]
[TD="align: left"]=B14&"_"&COUNTIF($B$2:B14,B14)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



What you now have in Column A is a unique identifier so customer F223 has 3 orders. Order 1 is on the row that contains F223_1, order 2 is on the row with F223_2 etc.

This means you can now use VLOOKUP to pull through the data for the right order

e.g.

Assuming for the moment that your data is on a sheet called 'Data' ... to pull through the order ID's for whichever Persons ID is in cell A2 .. these formulas would work:

Order 1 ID =IFERROR(VLOOKUP($A2&"_"&1,Data!$A$2:$I$14,6,0),"")
Order 2 ID =IFERROR(VLOOKUP($A2&"_"&2,Data!$A$2:$I$14,6,0),"")
Order 3 ID =IFERROR(VLOOKUP($A2&"_"&3,Data!$A$2:$I$14,6,0),"")

Bear with me as I'm not sure how this will look:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P

<tbody>
[TD="align: center"]1
[/TD]
[TD="bgcolor: #FFFFFF"]Person Unique ID
[/TD]
[TD="bgcolor: #FFFFFF"]Name
[/TD]
[TD="bgcolor: #FFFFFF"]Surname
[/TD]
[TD="bgcolor: #FFFFFF"]Address
[/TD]
[TD="bgcolor: #FFFFFF"]Order 1 id
[/TD]
[TD="bgcolor: #FFFFFF"]Order 1 description
[/TD]
[TD="bgcolor: #FFFFFF"]order 1 date
[/TD]
[TD="bgcolor: #FFFFFF"]order 1 price
[/TD]
[TD="bgcolor: #FFFFFF"]Order 2 id
[/TD]
[TD="bgcolor: #FFFFFF"]Order 2 description
[/TD]
[TD="bgcolor: #FFFFFF"]order 2 date
[/TD]
[TD="bgcolor: #FFFFFF"]order 2 price
[/TD]
[TD="bgcolor: #FFFFFF"]Order 3 id
[/TD]
[TD="bgcolor: #FFFFFF"]Order 3 description
[/TD]
[TD="bgcolor: #FFFFFF"]order 3 date
[/TD]
[TD="bgcolor: #FFFFFF"]order 3 price
[/TD]

[TD="align: center"]2
[/TD]
[TD="bgcolor: #FFFFFF"]A44
[/TD]
[TD="bgcolor: #FFFFFF"]Alex
[/TD]
[TD="bgcolor: #FFFFFF"]Smith
[/TD]
[TD="bgcolor: #FFFFFF"]1 High Street
[/TD]
[TD="bgcolor: #FFFFFF"]5
[/TD]
[TD="bgcolor: #FFFFFF"]apples
[/TD]
[TD="bgcolor: #FFFFFF"]36526
[/TD]
[TD="bgcolor: #FFFFFF"]1
[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]

[TD="align: center"]3
[/TD]
[TD="bgcolor: #FFFFFF"]D516
[/TD]
[TD="bgcolor: #FFFFFF"]Diane
[/TD]
[TD="bgcolor: #FFFFFF"]Jones
[/TD]
[TD="bgcolor: #FFFFFF"]222 High Street
[/TD]
[TD="bgcolor: #FFFFFF"]4
[/TD]
[TD="bgcolor: #FFFFFF"]bananas
[/TD]
[TD="bgcolor: #FFFFFF"]36557
[/TD]
[TD="bgcolor: #FFFFFF"]45
[/TD]
[TD="bgcolor: #FFFFFF"]34
[/TD]
[TD="bgcolor: #FFFFFF"]grapes
[/TD]
[TD="bgcolor: #FFFFFF"]40271
[/TD]
[TD="bgcolor: #FFFFFF"]32
[/TD]
[TD="bgcolor: #FFFFFF"]56
[/TD]
[TD="bgcolor: #FFFFFF"]peas
[/TD]
[TD="bgcolor: #FFFFFF"]41065
[/TD]
[TD="bgcolor: #FFFFFF"]200
[/TD]

[TD="align: center"]4
[/TD]
[TD="bgcolor: #FFFFFF"]D700
[/TD]
[TD="bgcolor: #FFFFFF"]David
[/TD]
[TD="bgcolor: #FFFFFF"]Green
[/TD]
[TD="bgcolor: #FFFFFF"]55 High Road
[/TD]
[TD="bgcolor: #FFFFFF"]567
[/TD]
[TD="bgcolor: #FFFFFF"]peppers
[/TD]
[TD="bgcolor: #FFFFFF"]42097
[/TD]
[TD="bgcolor: #FFFFFF"]5000
[/TD]
[TD="bgcolor: #FFFFFF"]1234
[/TD]
[TD="bgcolor: #FFFFFF"]lettuce
[/TD]
[TD="bgcolor: #FFFFFF"]41397
[/TD]
[TD="bgcolor: #FFFFFF"]54
[/TD]
[TD="bgcolor: #FFFFFF"]2123
[/TD]
[TD="bgcolor: #FFFFFF"]olives
[/TD]
[TD="bgcolor: #FFFFFF"]40032
[/TD]
[TD="bgcolor: #FFFFFF"]10
[/TD]

[TD="align: center"]5
[/TD]
[TD="bgcolor: #FFFFFF"]E55
[/TD]
[TD="bgcolor: #FFFFFF"]Eric
[/TD]
[TD="bgcolor: #FFFFFF"]Martin
[/TD]
[TD="bgcolor: #FFFFFF"]100 High Road
[/TD]
[TD="bgcolor: #FFFFFF"]654
[/TD]
[TD="bgcolor: #FFFFFF"]strawberries
[/TD]
[TD="bgcolor: #FFFFFF"]42191
[/TD]
[TD="bgcolor: #FFFFFF"]20
[/TD]
[TD="bgcolor: #FFFFFF"]89
[/TD]
[TD="bgcolor: #FFFFFF"]potatoes
[/TD]
[TD="bgcolor: #FFFFFF"]41640
[/TD]
[TD="bgcolor: #FFFFFF"]30
[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]

[TD="align: center"]6
[/TD]
[TD="bgcolor: #FFFFFF"]F223
[/TD]
[TD="bgcolor: #FFFFFF"]Fiona
[/TD]
[TD="bgcolor: #FFFFFF"]Smith
[/TD]
[TD="bgcolor: #FFFFFF"]25 Church Street
[/TD]
[TD="bgcolor: #FFFFFF"]98
[/TD]
[TD="bgcolor: #FFFFFF"]salt
[/TD]
[TD="bgcolor: #FFFFFF"]39449
[/TD]
[TD="bgcolor: #FFFFFF"]44
[/TD]
[TD="bgcolor: #FFFFFF"]766
[/TD]
[TD="bgcolor: #FFFFFF"]sugar
[/TD]
[TD="bgcolor: #FFFFFF"]42165
[/TD]
[TD="bgcolor: #FFFFFF"]100
[/TD]
[TD="bgcolor: #FFFFFF"]4544
[/TD]
[TD="bgcolor: #FFFFFF"]jam
[/TD]
[TD="bgcolor: #FFFFFF"]42164
[/TD]
[TD="bgcolor: #FFFFFF"]10
[/TD]

[TD="align: center"]7
[/TD]
[TD="bgcolor: #FFFFFF"]G55
[/TD]
[TD="bgcolor: #FFFFFF"]Gary
[/TD]
[TD="bgcolor: #FFFFFF"]King
[/TD]
[TD="bgcolor: #FFFFFF"]100 Oxford Road
[/TD]
[TD="bgcolor: #FFFFFF"]65
[/TD]
[TD="bgcolor: #FFFFFF"]marmalade
[/TD]
[TD="bgcolor: #FFFFFF"]42163
[/TD]
[TD="bgcolor: #FFFFFF"]2
[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]

</tbody>

Result​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2&"_"&1,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F2
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2&"_"&1,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G2
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2&"_"&1,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H2
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2&"_"&1,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I2
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2&"_"&2,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J2
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2&"_"&2,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K2
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2&"_"&2,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]L2
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2&"_"&2,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]M2
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2&"_"&3,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]N2
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2&"_"&3,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]O2
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2&"_"&3,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]P2
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A2&"_"&3,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E3
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A3&"_"&1,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F3
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A3&"_"&1,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G3
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A3&"_"&1,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H3
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A3&"_"&1,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I3
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A3&"_"&2,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J3
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A3&"_"&2,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K3
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A3&"_"&2,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]L3
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A3&"_"&2,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]M3
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A3&"_"&3,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]N3
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A3&"_"&3,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]O3
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A3&"_"&3,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]P3
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A3&"_"&3,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E4
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A4&"_"&1,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F4
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A4&"_"&1,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G4
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A4&"_"&1,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H4
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A4&"_"&1,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I4
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A4&"_"&2,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J4
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A4&"_"&2,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K4
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A4&"_"&2,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]L4
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A4&"_"&2,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]M4
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A4&"_"&3,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]N4
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A4&"_"&3,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]O4
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A4&"_"&3,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]P4
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A4&"_"&3,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E5
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A5&"_"&1,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F5
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A5&"_"&1,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G5
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A5&"_"&1,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H5
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A5&"_"&1,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I5
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A5&"_"&2,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J5
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A5&"_"&2,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K5
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A5&"_"&2,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]L5
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A5&"_"&2,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]M5
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A5&"_"&3,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]N5
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A5&"_"&3,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]O5
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A5&"_"&3,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]P5
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A5&"_"&3,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E6
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A6&"_"&1,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F6
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A6&"_"&1,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G6
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A6&"_"&1,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H6
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A6&"_"&1,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I6
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A6&"_"&2,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J6
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A6&"_"&2,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K6
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A6&"_"&2,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]L6
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A6&"_"&2,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]M6
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A6&"_"&3,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]N6
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A6&"_"&3,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]O6
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A6&"_"&3,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]P6
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A6&"_"&3,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E7
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A7&"_"&1,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F7
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A7&"_"&1,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G7
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A7&"_"&1,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H7
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A7&"_"&1,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I7
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A7&"_"&2,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J7
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A7&"_"&2,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K7
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A7&"_"&2,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]L7
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A7&"_"&2,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]M7
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A7&"_"&3,Data!$A$2:$I$14,6,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]N7
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A7&"_"&3,Data!$A$2:$I$14,7,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]O7
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A7&"_"&3,Data!$A$2:$I$14,8,0),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]P7
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP($A7&"_"&3,Data!$A$2:$I$14,9,0),"")
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


I don't know your level of excel, so i didn't want to throw too many things at you, but what I would personally do, to make it even easier, is insert two rows at the top of your 'results' in order to replace a few bits in the formula and make them really easy to copy across the whole range. e.g. replace the part that says &"_"&1, &"_"&2 etc. with a cell reference that contains the numbers 1, 2 & 3. So Row 1 would include the order numbers, Row 2 the column number for the vlookup and the new formula for Order 1 ID would be:

=IFERROR(VLOOKUP($A4&"_"&E$1,Data!$A$2:$I$14,E$2,0),"")

 

[Zakkaroo]

Perhaps easier to see it!

https://docs.google.com/spreadsheets/d/1RTLmu-YQA69coeuUy279RDm9W5Q2HEQ7tN3KBMrxOjc/edit?usp=docslist_api<o></o>

 

[MickG]

Try this:-
Results sheet2.

[COLOR=Navy]Sub[/COLOR] MG10Jun55
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] C [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Rw [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] oMax [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Q [COLOR=Navy]As[/COLOR] Variant, Dic [COLOR=Navy]As[/COLOR] Object, Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Sp [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Dim[/COLOR] oRay [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]With[/COLOR] ActiveSheet
  oRay = .Range("A1").CurrentRegion
[COLOR=Navy]End[/COLOR] With

ReDim ray(1 To UBound(oRay, 1), 1 To UBound(oRay, 2))
    [COLOR=Navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare

[COLOR=Navy]For[/COLOR] Rw = 1 To UBound(oRay, 1)
    [COLOR=Navy]If[/COLOR] Not Dic.Exists(oRay(Rw, 1)) [COLOR=Navy]Then[/COLOR]
        n = n + 1
        [COLOR=Navy]For[/COLOR] Ac = 1 To UBound(oRay, 2)
            Sp = Split(oRay(Rw, Ac), " ")
            [COLOR=Navy]If[/COLOR] Rw = 1 And Ac > 4 [COLOR=Navy]Then[/COLOR]
                ray(n, Ac) = Sp(0) & " 1 " & Sp(1)
            [COLOR=Navy]Else[/COLOR]
                ray(n, Ac) = oRay(Rw, Ac)
            [COLOR=Navy]End[/COLOR] If
        [COLOR=Navy]Next[/COLOR] Ac
        Dic.Add oRay(Rw, 1), Array(n, UBound(oRay, 2), 1)
    [COLOR=Navy]Else[/COLOR]
        Q = Dic.Item(oRay(Rw, 1))
            oMax = Application.Max(oMax, Q(1))
            Q(1) = Q(1) + 4
            Q(2) = Q(2) + 1
            [COLOR=Navy]If[/COLOR] UBound(ray, 2) < Q(1) [COLOR=Navy]Then[/COLOR] ReDim Preserve ray(1 To UBound(oRay, 1), 1 To Q(1))
                [COLOR=Navy]For[/COLOR] Ac = 1 To 4
                    Sp = Split(oRay(1, Ac + 4), " ")
                    ray(1, Q(1) - 4 + Ac) = Sp(0) & " " & Q(2) & " " & Sp(1)
                    ray(Q(0), Q(1) - 4 + Ac) = oRay(Rw, Ac + 4)
                [COLOR=Navy]Next[/COLOR] Ac
        Dic.Item(oRay(Rw, 1)) = Q
   [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]

[COLOR=Navy]Set[/COLOR] Rng = Sheets("Sheet2").Range("A1").Resize(UBound(ray, 1), UBound(ray, 2))
     Rng.Value = ray
     Rng.Columns.AutoFit
[COLOR=Navy]With[/COLOR] Sheets("Sheet2")
       .Sort.SortFields.Add Key:=Rng.Resize(, 1) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    [COLOR=Navy]With[/COLOR] .Sort
        .SetRange Rng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    [COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[dave501]

Thank you both so much for taking the time to help. MickG, thanks very much but I wasn't able to follow this code to modify it. Zakkaroo, thanks that is great, I have been able to use your solution and it is working great. Thank you so much! I had tried Vlookup before but couldn't get it work as no unique value, your trick is very clever!