Reformat Data based on Multiple Unique Values for fixed List

[hassanleo1987]

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!

 

[hassanleo1987]

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2				Data:						Result:
3		Unique		Left	Right	Value 1	Value 2			Value 1	Value 2	Left	Unique	Right	Value 1	Value 2
4		A		C	E	1	0			0	1	G	A	C	1	0
5		B		F	K	2	40
6		C		C	D	1	0			0	1	I	B	C	2	40
7		D		D	L	1	0
8		E		C	M	1	25			0	1	A	C	E	1	0
9		F		F	N	3	25			40	2	B	C	D	1	0
10				A	C	1	0						C	M	1	25
11				D	O	2	50
12				E	F	1	0			0	1	C	D	L	1	0
13				B	C	2	40						D	O	2	50
14				D	P	1	0						D	P	1	0
15				F	Q	2	25
16				F	R	2	25			0	1	C	E	F	1	0
17				F	S	1	0			0	1	H	E
18				C	D	1	0
19				G	A	1	0			0	1	H	F	K	2	40
20				A	C	1	0			0	1	E	F	N	3	25
21				H	E	1	0			0	3	J	F	Q	2	25
22				H	F	1	0						F	R	2	25
23				E	F	1	0						F	S	1	0
24				B	C	2	40
25				I	B	1	0
26				J	F	3	0
27
Data

Cell Formulas
Range	Formula
J4:J24	J4	=IFERROR(INDEX(Data!$G$4:$G$26,MATCH(1,($M4=Data!$E$4:$E$26)*($L4=Data!$D$4:$D$26),0)),"")
K4:K24	K4	=IFERROR(INDEX(Data!$F$4:$F$26,MATCH(1,($M4=Data!$E$4:$E$26)*($L4=Data!$D$4:$D$26),0)),"")
O4:O24	O4	=IFERROR(INDEX(Data!$F$4:$F$26,MATCH(1,($M4=Data!$D$4:$D$26)*($N4=Data!$E$4:$E$26),0)),"")
P4:P24	P4	=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
Cell	Condition		Cell Format	Stop If True
D4:E26	Cell Value	="F"	text	NO
D4:E26	Cell Value	="E"	text	NO
D4:E26	Cell Value	="D"	text	NO
D4:E26	Cell Value	="C"	text	NO
D4:E26	Cell Value	="B"	text	NO
D4:E26	Cell Value	="A"	text	NO

Here is the example

 

[hassanleo1987]

@Akuini please take a look, If you need any explanation, I am available!

 

[Akuini]

I have a large data set of multiple columns (maximum 10)

1. So there could be more column?
2. How many unique values are there? hundreds?

 

[Akuini]

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

 

[hassanleo1987]

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]

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]

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]

One more question:
Is it possible that some unique values doesn't exist in the Data table?

 

[hassanleo1987]

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.