
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)
