Active Member
March 12, 2019
Dear Mynda
Request your help in consolidating the data in the following manner.
Attached the excel file
Have pivoted Data like this :- | Need to consolidate like this:- | |||
Region Name | Branch Codes | Region Name | Branch Codes | |
ANANTHAPUR | ANTP3 | ANANTHAPUR | ANTP3; ANTPR; BDVLE | |
ANANTHAPUR | ANTPR | GUNTUR | CHPET; GUNBR; MCHLA; MGIRI; PIDU1; TENLI | |
ANANTHAPUR | BDVLE | KUKATPALLY | KOMPL; KPHB1; KPHBR; LNGP2; LNGPY; MDCHA; MEDKB; SHMPT; SNGRD; ZAHBD | |
GUNTUR | CHPET | L B NAGAR | IBRPM; NGOL1; NGOLE; SMBAD | |
GUNTUR | GUNBR | MAHABUBNAGAR | GADWA; MAHAB; NAGKL; PRIGI; SHANT; SHDNR | |
GUNTUR | MCHLA | NIZAMABAD | MANTN; MNCHL | |
GUNTUR | MGIRI | ONGOLE | ADANK; BETHM; CHRLA; KNDKU; KNGIR; KOKTL; KURN1; KURNO; MRKPM; NADYL; ONGOL | |
GUNTUR | PIDU1 | RAJAHMUNDRY | AMLP2; BMVRM; JNGRD; KKNB3; KKND1; KKND2; PLAKU; RAJA3; RAJAH; TANKU | |
GUNTUR | TENLI | TARNAKA | CHEVL; HMTNR; MEDPL; TANDR; TCHO1; YADBR | |
KUKATPALLY | KOMPL | TIRUPATHI | CTOOR; CTOR1; KAVLI; MADAN; MADAP; NADPT; TRPA2 | |
KUKATPALLY | KPHB1 | VIJAYAWADA | KKLUR | |
KUKATPALLY | KPHBR | VISAKAPATNAM | GAJU2; NRPTM | |
KUKATPALLY | LNGP2 | VIZIANAGARAM | RAJMB | |
KUKATPALLY | LNGPY | WARANGAL | ||
KUKATPALLY | MDCHA | |||
KUKATPALLY | MEDKB | |||
KUKATPALLY | SHMPT | |||
KUKATPALLY | SNGRD | |||
KUKATPALLY | ZAHBD | |||
L B NAGAR | IBRPM | |||
L B NAGAR | NGOL1 | |||
L B NAGAR | NGOLE | |||
L B NAGAR | SMBAD | |||
MAHABUBNAGAR | GADWA | |||
MAHABUBNAGAR | MAHAB | |||
MAHABUBNAGAR | NAGKL | |||
MAHABUBNAGAR | PRIGI | |||
MAHABUBNAGAR | SHANT | |||
MAHABUBNAGAR | SHDNR | |||
NIZAMABAD | MANTN | |||
NIZAMABAD | MNCHL | |||
ONGOLE | ADANK | |||
ONGOLE | BETHM | |||
ONGOLE | CHRLA | |||
ONGOLE | KNDKU | |||
ONGOLE | KNGIR | |||
ONGOLE | KOKTL | |||
ONGOLE | KURN1 | |||
ONGOLE | KURNO | |||
ONGOLE | MRKPM | |||
ONGOLE | NADYL | |||
ONGOLE | ONGOL | |||
RAJAHMUNDRY | AMLP2 | |||
RAJAHMUNDRY | BMVRM | |||
RAJAHMUNDRY | JNGRD | |||
RAJAHMUNDRY | KKNB3 | |||
RAJAHMUNDRY | KKND1 | |||
RAJAHMUNDRY | KKND2 | |||
RAJAHMUNDRY | PLAKU | |||
RAJAHMUNDRY | RAJA3 | |||
RAJAHMUNDRY | RAJAH | |||
RAJAHMUNDRY | TANKU | |||
TARNAKA | CHEVL | |||
TARNAKA | HMTNR | |||
TARNAKA | MEDPL | |||
TARNAKA | TANDR | |||
TARNAKA | TCHO1 | |||
TARNAKA | YADBR | |||
TIRUPATHI | CTOOR | |||
TIRUPATHI | CTOR1 | |||
TIRUPATHI | KAVLI | |||
TIRUPATHI | MADAN | |||
TIRUPATHI | MADAP | |||
TIRUPATHI | NADPT | |||
TIRUPATHI | TRPA2 | |||
VIJAYAWADA | KKLUR | |||
VISAKAPATNAM | GAJU2 | |||
VISAKAPATNAM | NRPTM | |||
VIZIANAGARAM | RAJMB |
Kind Regards,
A J M Raghu
Trusted Members
Moderators
November 1, 2018
Depending on what version of Power Pivot you have, you may be able to use CONCATENATEX for this: https://www.mrexcel.com/excel-.....lues-area/
Power Query
Power Pivot
Power BI
October 17, 2018
Active Member
March 12, 2019
Velouria said
Depending on what version of Power Pivot you have, you may be able to use CONCATENATEX for this: https://www.mrexcel.com/excel-.....-area/
Thanks for responding so quickly. However, in my system, i don't have Power Pivot.
I think its Excel 2013. in which i could not find "Data to Data Model" option, not did if find "Add Measures" Option
Hence, request you to plz suggest accordingly
thanks & regards
Raghu
Trusted Members
Moderators
November 1, 2018
Which version of 2013? If it's Professional Plus, or Excel standalone, you should have Power Pivot. If it's a different version, you cannot use it and you will not be able to do what you want with a pivot table, so you will want code. Please post an example workbook if you want code.
VIP
Trusted Members
June 25, 2016
Trusted Members
Moderators
November 1, 2018
Sunny has already given you a great answer, but since I'd done it anyway, and in case your data set isn't sorted, you could also try this:
Sub ConsolidateData()
' change first column letter as necessary
Dim FirstColumn As String
FirstColumn = "A"
' change the start row (after the header) if necessary
Dim FirstDataRow As Long
FirstDataRow = 3
Dim LastRow As Long
LastRow = Cells(Rows.Count, FirstColumn).End(xlUp).Row
Dim DataRange As Range
Set DataRange = Cells(FirstDataRow, FirstColumn).Resize(LastRow - FirstDataRow + 1, 2)
Dim DataSet
DataSet = DataRange.Value
Application.ScreenUpdating = False
DataRange.Offset(, DataRange.Columns.Count + 1).EntireColumn.Clear
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Dim i As Long
For i = LBound(DataSet) To UBound(DataSet)
If Not dic.exists(DataSet(i, 1)) Then
dic(DataSet(i, 1)) = DataSet(i, 2)
Else
dic(DataSet(i, 1)) = dic(DataSet(i, 1)) & ";" & DataSet(i, 2)
End If
Next i
If dic.Count > 0 Then
With DataRange.Rows(1)
.Offset(-1, .Columns.Count + 1).Value = .Offset(-1).Value
With .Offset(, .Columns.Count + 1).Resize(dic.Count, .Columns.Count)
.Value = Application.Transpose(Array(dic.keys, dic.items))
.EntireColumn.AutoFit
End With
End With
End If
End Sub
Answers Post
1 Guest(s)