convert multiple rows to one row per person with additional columns

dave501

New Member
Joined
Jun 10, 2015
Messages
2
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 IDNameSurnameAddressOrder idOrder descriptionorder dateorder price
A44AlexSmith1 High Street5apples01/01/20001
D516DianeJones222 High Street4bananas01/02/200045
D516DianeJones222 High Street34grapes03/04/201032
D516DianeJones222 High Street56peas05/06/2012200
D700DavidGreen55 High Road567peppers03/04/20155000
D700DavidGreen55 High Road1234lettuce03/05/201354
D700DavidGreen55 High Road2123olives07/08/200910
E55EricMartin100 High Road654strawberries06/07/201520
E55EricMartin100 High Road89potatoes01/01/201430
F223FionaSmith25 Church Street98salt02/01/200844
F223FionaSmith25 Church Street766sugar10/06/2015100
F223FionaSmith25 Church Street4544jam09/06/201510
G55GaryKing100 Oxford Road65marmalade08/06/20152

<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 IDNameSurnameAddressOrder 1 idOrder 1 descriptionorder 1 dateorder 1 priceOrder 2 idOrder 2 descriptionorder 2 dateorder 2 priceOrder 3 idOrder 3 descriptionorder 3 dateorder 3 price
A44AlexSmith1 High Street5apples01/01/20001
D516DianeJones222 High Street4bananas01/02/20004534grapes03/04/20103256peas05/06/2012200
D700DavidGreen55 High Road567peppers03/04/201550001234lettuce03/05/2013542123olives07/08/200910
E55EricMartin100 High Road654strawberries06/07/20152089potatoes01/01/201430
F223FionaSmith25 Church Street98salt02/01/200844766sugar10/06/20151004544jam09/06/201510
G55GaryKing100 Oxford Road65marmalade08/06/20152

<tbody>
</tbody>

Does anyone have any advice on how to achieve this?

Thanks,
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Zakkaroo

Active Member
Joined
Jul 6, 2009
Messages
383
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),"")
 
Last edited:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Results sheet2.
Code:
[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

New Member
Joined
Jun 10, 2015
Messages
2
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,696
Messages
5,637,859
Members
416,986
Latest member
zmartee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top