Reformat Data based on Multiple Unique Values for fixed List

hassanleo1987

Board Regular
Joined
Apr 19, 2017
Messages
53
Hi,

I have a large data set of multiple columns (maximum 10) and rows more than 50K.
The data set has 2 parts.
1st part included a single column of unique values (alpha-numeric) only. let call it IDs
2nd part is a table where unique values from part 1 are linked together with 2 separate values which we'll call left and right.
The linkup is called 2-way relationship, which means IDs will have 2 types of values, one of right side and one on left side. When looking for unique values on the right side, the ID value will be filters from left column and vice versa.
There are duplications of data when filtering this way but they are ignored.

I need to get this list sorted in a way the for every ID value from part 1, there will be 2 side of table where unique values of right and left column are listed. The number of values on left and right are variable so which ever value is maximum, will determine the number of rows needed for each ID. While looking up unique left and right values, the 2 Value columns i.e., Value 1 and Value 2 will be looked up for both sides.

Final step is to apply formatting, each unique ID along with its left, right and values should be in a single border box to identify as unique set.

Currently I am all these steps using arrays, which takes a lot of time and I have to do it in multiple parts.

Is there a way, this could be done using VBA where I can specify the ID column, Left, right and value columns and the code will sort the data in the required format.

Please if somebody can help!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

hassanleo1987

Board Regular
Joined
Apr 19, 2017
Messages
53
Example.xlsx
ABCDEFGHIJKLMNOPQ
1
2Data:Result:
3UniqueLeftRightValue 1Value 2Value 1Value 2LeftUniqueRightValue 1Value 2
4ACE1001GAC10
5BFK240    
6CCD1001IBC240
7DDL10    
8ECM12501ACE10
9FFN325402BCD10
10AC10  CM125
11DO250    
12EF1001CDL10
13BC240  DO250
14DP10  DP10
15FQ225    
16FR22501CEF10
17FS1001HE  
18CD10    
19GA1001HFK240
20AC1001EFN325
21HE1003JFQ225
22HF10  FR225
23EF10  FS10
24BC240    
25IB10
26JF30
27
Data
Cell Formulas
RangeFormula
J4:J24J4=IFERROR(INDEX(Data!$G$4:$G$26,MATCH(1,($M4=Data!$E$4:$E$26)*($L4=Data!$D$4:$D$26),0)),"")
K4:K24K4=IFERROR(INDEX(Data!$F$4:$F$26,MATCH(1,($M4=Data!$E$4:$E$26)*($L4=Data!$D$4:$D$26),0)),"")
O4:O24O4=IFERROR(INDEX(Data!$F$4:$F$26,MATCH(1,($M4=Data!$D$4:$D$26)*($N4=Data!$E$4:$E$26),0)),"")
P4:P24P4=IFERROR(INDEX(Data!$G$4:$G$26,MATCH(1,($M4=Data!$D$4:$D$26)*($N4=Data!$E$4:$E$26),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D4:E26Cell Value="F"textNO
D4:E26Cell Value="E"textNO
D4:E26Cell Value="D"textNO
D4:E26Cell Value="C"textNO
D4:E26Cell Value="B"textNO
D4:E26Cell Value="A"textNO


Here is the example
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,653
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

One more question:
3. Are the unique value in col B sorted ascending? if not then is it ok to sort it?
 

hassanleo1987

Board Regular
Joined
Apr 19, 2017
Messages
53
1. So there could be more column?
2. How many unique values are there? hundreds?
Yes. But they will act like the Value 1 and Value 2 columns. No much of trouble.
The unique value / ID column can be as big as 10,000 values.
That is why the 2 way relationship data set could be as big as 50,000 rows.

I have done it by breaking it up in small steps.

Using pivot table to get the Left and right unique values for each ID, Make a list of count for unique left and right values for each ID. Get the max no of rows by comparing both number of unique left and right values.

Found a small code to add number of rows based on cell value.

Now when I started populating the left and right column as shown in result table, I have to use array to get the unique values based on ID criteria but the data set is too big for excel to handle via arrays.

I had waited for at least 2 hours but no results because was stuck in calculating the array for each ID.

That is why I started to look for a way to do this in VBA.
 

hassanleo1987

Board Regular
Joined
Apr 19, 2017
Messages
53

ADVERTISEMENT

One more question:
3. Are the unique value in col B sorted ascending? if not then is it ok to sort it?
No sorting is required for the unique values but ascending will be OK, just for the sake of presentation!
 

hassanleo1987

Board Regular
Joined
Apr 19, 2017
Messages
53
1. So there could be more column?
2. How many unique values are there? hundreds?
Actually, If this had been a once kind of work, would be OK to do with excel formulation, but this data transformation is required for a biweekly / monthly update which makes it more time sensitive issue. My real problems lies after getting the data in shape. to compare both sets as quickly as possible making an assessment of every change that has been made in the time duration of updates. Last step for me is to justify the changes which is the toughest part.

Automation of this data conversion will save me a lot of time and help to focus on real problems!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,653
Office Version
  1. 365
Platform
  1. Windows
One more question:
Is it possible that some unique values doesn't exist in the Data table?
 

hassanleo1987

Board Regular
Joined
Apr 19, 2017
Messages
53
One more question:
Is it possible that some unique values doesn't exist in the Data
No, All unique values will be there in 2 way relationship table. Either in Left or Right column. Also there may be duplication as I have shown in example for unique ID A. If you filter A in left to get right values. you will see the relationship twice.

Such duplication exist because, the data set is exported from a Database where every relationship duplicate or otherwise has a unique reference number.
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,579
Messages
5,770,960
Members
425,653
Latest member
UNSING

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