Another transposition query

Malcolm Gill

New Member
Joined
Nov 26, 2016
Messages
18
hiker95 sorted one query for me. Can anybody out there help with this one?

I have an Excel file of some 1600 rows. Here are the first few rows of 6 columns with headers from the start of the file.

gkeygroup_nameforenamesurnameaddedwaitingleader
3811B1 Chicago BridgeLaurieAdamson23/10/2016
3793B4 Chicago BridgeLaurieAdamson23/10/2016
3792B6 Chicago BridgeLaurieAdamson23/10/2016
3784A1 Art Workshop 1EdelAguero03/11/2016
3818A5 WatercoloursEdelAguero03/11/2016
3815K6 Keep FitEdelAguero03/11/2016
3811B1 Chicago BridgeSylviaAllen23/10/2016
3793B4 Chicago BridgeSylviaAllen23/10/2016
3805BA2 Improvers BRosemaryAllen23/10/20161
3856BA3 Duplicate ImpRosemaryAllen23/10/2016
3807K3 Line DancingRosemaryAllen23/10/2016
3811B1 Chicago BridgeGregAnsell13/10/20161
3811B1 Chicago BridgeTiggyAnsell23/10/2016
3796B3 Duplicate BridgeGregAnsell23/10/2016
3796B3 Duplicate BridgeTiggyAnsell23/10/2016
3855A7 Ten PaintingsPamApplin23/10/2016
3834J1 DorsetPamApplin23/10/2016
3847N2 Wildlife BritainPamApplin23/10/2016
3866W7 The Silk RoadsPamApplin29/11/2016
3853K13 Latin DancingAnnArthur-Ling23/10/2016
3807K3 Line DancingAnnArthur-Ling23/10/2016
3862L11 Beginners GAnnArthur-Ling23/10/20161
3790BA1 BeginnersKenAshman23/10/2016
3834J1 DorsetKenAshman23/10/2016
3851T7 Explore MathsKenAshman28/11/2016
3823W6 Film StudiesKenAshman23/10/2016
3825Z1 3 MILE WALKSKenAshman23/10/2016
3826Z2 5 MILE PUB WALKSKenAshman23/10/2016
3843H9 Military HistoryDavidAshover23/10/2016

<tbody>
</tbody>


What I would like is

forenamesurnamegkey1gkey2gkey3etc
LaurieAdamson381137933792
EdelAguero378438183815
SylviaAllen38113793
RosemaryAllen380538563807
GregAnsell38113796
TiggyAnsell38113796
PamApplin3855383438473866
AnnArthur-Ling385338073862
KenAshman3790383438513823
DavidAshover3843

<tbody>
</tbody>

There can be more than one occurrence of surname but with different forename. Each person may have up to 10 gkeys.

I'd be truly grateful for assistance!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for results on sheet2.
Code:
[COLOR="Navy"]Sub[/COLOR] MG08Dec17
[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] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count, 1 To 3)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = Dn.Text & Dn.Offset(, 1).Text
        [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            Ray(n, 1) = Dn.Text: Ray(n, 2) = Dn.Offset(, 1).Text
            Ray(n, 3) = Dn.Offset(, -2).Value
            .Add Txt, Array(n, 3)
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Txt)
            Q(1) = Q(1) + 1
            [COLOR="Navy"]If[/COLOR] UBound(Ray, 2) < Q(1) [COLOR="Navy"]Then[/COLOR] ReDim Preserve Ray(1 To Rng.Count, 1 To Q(1))
            Ray(Q(0), Q(1)) = Dn.Offset(, -2).Value
            Ray(1, Q(1)) = "gkey" & Q(1) - 2
            .Item(Txt) = Q
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Rw = .Count
Ray(1, 3) = "gkey1"
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(Rw, UBound(Ray, 2))
    .Value = Ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,483
Members
414,146
Latest member
marginmakerb

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