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.

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

<tbody>
</tbody>

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.

Person Unique ID

Name

Surname

Address

Order 1 id

Order 1 description

order 1 date

order 1 price

Order 2 id

Order 2 description

order 2 date

order 2 price

Order 3 id

Order 3 description

order 3 date

order 3 price

A44

Alex

Smith

1 High Street

5

apples

01/01/2000

1

D516

Diane

Jones

222 High Street

4

bananas

01/02/2000

45

34

grapes

03/04/2010

32

56

peas

05/06/2012

200

D700

David

Green

55 High Road

567

peppers

03/04/2015

5000

1234

lettuce

03/05/2013

54

2123

olives

07/08/2009

10

E55

Eric

Martin

100 High Road

654

strawberries

06/07/2015

20

89

potatoes

01/01/2014

30

F223

Fiona

Smith

25 Church Street

98

salt

02/01/2008

44

766

sugar

10/06/2015

100

4544

jam

09/06/2015

10

G55

Gary

King

100 Oxford Road

65

marmalade

08/06/2015

2

<tbody>
</tbody>

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
1	Unique Identifier	Person Unique ID
2	A44_1	A44
3	D516_1	D516
4	D516_2	D516
5	D516_3	D516
6	D700_1	D700
7	D700_2	D700
8	D700_3	D700
9	E55_1	E55
10	E55_2	E55
11	F223_1	F223
12	F223_2	F223
13	F223_3	F223
14	G55_1	G55

<tbody>
</tbody>

Data​

Worksheet Formulas Cell Formula A2 =B2&"_"&COUNTIF($B$2:B2,B2) A3 =B3&"_"&COUNTIF($B$2:B3,B3) A4 =B4&"_"&COUNTIF($B$2:B4,B4) A5 =B5&"_"&COUNTIF($B$2:B5,B5) A6 =B6&"_"&COUNTIF($B$2:B6,B6) A7 =B7&"_"&COUNTIF($B$2:B7,B7) A8 =B8&"_"&COUNTIF($B$2:B8,B8) A9 =B9&"_"&COUNTIF($B$2:B9,B9) A10 =B10&"_"&COUNTIF($B$2:B10,B10) A11 =B11&"_"&COUNTIF($B$2:B11,B11) A12 =B12&"_"&COUNTIF($B$2:B12,B12) A13 =B13&"_"&COUNTIF($B$2:B13,B13) A14 =B14&"_"&COUNTIF($B$2:B14,B14) <tbody> </tbody>

<tbody>
</tbody>

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
1	Person Unique ID	Name	Surname	Address	Order 1 id	Order 1 description	order 1 date	order 1 price	Order 2 id	Order 2 description	order 2 date	order 2 price	Order 3 id	Order 3 description	order 3 date	order 3 price
2	A44	Alex	Smith	1 High Street	5	apples	36526	1
3	D516	Diane	Jones	222 High Street	4	bananas	36557	45	34	grapes	40271	32	56	peas	41065	200
4	D700	David	Green	55 High Road	567	peppers	42097	5000	1234	lettuce	41397	54	2123	olives	40032	10
5	E55	Eric	Martin	100 High Road	654	strawberries	42191	20	89	potatoes	41640	30
6	F223	Fiona	Smith	25 Church Street	98	salt	39449	44	766	sugar	42165	100	4544	jam	42164	10
7	G55	Gary	King	100 Oxford Road	65	marmalade	42163	2

<tbody>
</tbody>

Result​

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

<tbody>
</tbody>

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!