Table Comparisons

[cotech_10]

Hi all,

I have a spreadsheet with two tables of numerical data as per Figure 1

LH_Table Analysis.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA
1	Offset #		40	32	817	66	309	103	334			Table 1																				Offset #		12	100	69	55	200	45	99			Table 2
2
3		Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10	Col11	Col12	Col13	Col14	Col15	Col16	Col17	Col18	Col19	Col20												Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10	Col11	Col12	Col13	Col14	Col15	Col16	Col17	Col18	Col19	Col20
4	Line 1	40	90	48	324	615	70									32																Line 1	33	12	67	8	25	89									200
5	Line 2	339	605	49	666	716																										Line 2	50	55	60	69	88	213	434
6	Line 3	323	699	107	999	817																										Line 3	17	99	333
7	Line 4	66	545																													Line 4	201	210	506	550
8	Line 5	77	442	208																												Line 5	332
9	Line 6	88	128	309	421																											Line 6	110	100	45
10	Line 7	101	129																													Line 7	121	131
11	Line 8	102	148																													Line 8	141	82
12	Line 9	103																														Line 9	77
13	Line 10																															Line 10	76
14	Line 11																															Line 11
15	Line 12	110																														Line 12
16	Line 13																															Line 13	75
17	Line 14	232																														Line 14
18	Line 15	334																														Line 15	66
19	Line 16																															Line 16	55
20	Line 17																															Line 17
21	Line 18																															Line 18
22	Line 19																															Line 19
23	Line 20																															Line 20
24	Line 21																															Line 21
25	Line 22																															Line 22
26	Line 23																															Line 23
27	Line 24																															Line 24
28	Line 25																															Line 25
29	Line 26	99																														Line 26
30	Line 27																															Line 27	401
31	Line 28																															Line 28
32	Line 29																															Line 29
33	Line 30																															Line 30
34	Line 31																															Line 31
35	Line 32																															Line 32
36	Line 33																															Line 33
37	Line 34																															Line 34
38	Line 35																															Line 35
39	Line 36																															Line 36
40	Line 37																															Line 37
41	Line 38																															Line 38
42	Line 39																															Line 39
43	Line 40																															Line 40
44	Line 41																															Line 41
45	Line 42																															Line 42
46	Line 43																															Line 43
47	Line 44																															Line 44
48	Line 45																															Line 45
49	Line 46																															Line 46
50	Line 47																															Line 47
51	Line 48																															Line 48
52	Line 49																															Line 49
Main

In each Table there can be a maximum of 63 Rows and 20 columns.

I would like to compare and analyze the actual physical offset of
a set of numbers appearing in each table.



Task 1.

I would like to firstly colour the cells located in Tables 1 & 2 respectively that match the offset numbers.

In Row 1 Column D to Column J there are 7 offset numbers in those cells that relate to Table 1

In Row 1 Column AI to AO there are also 7 offset numbers in those cells that relate to Table 2

In this example the offset numbers in Table 1 are 40, 32, 817, 66, 309, 103 and 334 and in Table 1 they are colored yellow

In Table 2 the offset numbers are 12, 100, 69, 55, 200, 45 and 99 and also have been colored yellow

Task 2.

The next step is to analyze the actual cell Row and Column locations of
each individual offset number.

In this example:

The row and column details for the offset numbers in appearing in Table 1 are as follows:

Offset Number 40 : Line 1 Col1
Offset Number 32 : Line 1 Col15
Offset Number 817 : Line 3 Col5
Offset Number 66 : Line 4 Col1
Offset Number 309 : Line 6 Col3
Offset Number 103 : Line 9 Col1
Offset Number 334 : Line 15 Col1

The row and column details for the offset numbers in appearing in Table 2 are as follows:

Offset Number 12 : Line 1 Col2
Offset Number 100 : Line 6 Col2
Offset Number 96 : Line 2 Col4
Offset Number 55 : Line 16 Col1
Offset Number 200 : Line 1 Col15
Offset Number 45 : Line 6 Col3
Offset Number 99 : Line 3 Col2


I am looking for repeat positional locations, so from the data example above I would
end up with the following stats:

The repeated locations of number data occur at the following Rows and Columns:


Line 1 Col15 (Numbers 32 & 200 occupy these locations in the their respective Tables) marked with thick Black border
Line 6 Col3 (Numbers 309 & 45 occupy these locations in the their respective Tables) marked with thick Black border

The results would then be written to a Summary Table named "Summary Table - Part A" in a Worksheet TAB called "Summary"

Please refer to Figure 2 below:

LH_Table Analysis.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1				Summary Table - Part A
2										Number of Matching Locations	Number of Translations
3		P1	P2	P3	P4	P5	P6	P7
4	Lvl0		32			309				2	6
5	Lvl1
6	Lvl2
7	Lvl3
8	Lvl4
9	Lvl5
10	Lvl6
11	Lvl7
12	Lvl8
13	Lvl9
14	Lvl10
Summary

Task 3.


In addition to this in Table 1 of the MAIN sheet I would like to colour the cell locations where there are no repeats with the colour Blue and
that are a direct translation of number locations from Table 2 as per Figure 1.

So from the example above


Table 2 data is as follows

Offset Number 12 : Line 1 Col2 >>> translates to Table 1 location Line 1 Col2 Number = 90
Offset Number 100 : Line 6 Col2 >>> translates to Table 1 location Line 6 Col2 Number = 128
Offset Number 96 : Line 2 Col4 >>> translates to Table 1 location Line 2 Col4 Number = 666
Offset Number 55 : Line 16 Col1 >>> translates to Table 1 location Line 16 Col1 Number = 0 ( no value found)

not processed as it has repeated row and column locations **** Offset Number 200 : Line 1 Col15

not processed as it has repeated row and column locations **** Offset Number 45 : Line 6 Col3

Offset Number 99 : Line 3 Col2 >>> translates to Table 1 location Line 3 Col2 Number = 699


Therefore the numbers from a direct translation are:

1. 90
2. 128
3. 666
4. no value
5. repeated row and column
6. repeated row and column
7. 699

I would like to write this data to the Translation Table labelled "Translations From and To Table " in the "Summary" Sheet as per Figure 3
where it would detail a 'From' and 'To' translation event

LH_Table Analysis.xlsm
	N	O	P	Q	R	S	T	U
2		Translations From and To Table				Translations From and To Table
3		From	To			From	To
4	Lvl 0	12	90		Lvl 1	etc	etc
5		100	128			etc	etc
6		96	666			etc	etc
7		55	0			etc	etc
8		200	n/a			etc	etc
9		45	n/a			etc	etc
10		99	699			etc	etc
11
Summary

Task 4.

In the "Summary Table - Part A" Table there are (7) columns with headings Labelled "P1 to P7" and Rows labelled "Lvl0 to Lvl10"

From the above results we get:

Line 1 Col15 (Numbers 32 & 200 occupy these locations in the their respective Tables) marked with thick Black border
Line 6 Col3 (Numbers 309 & 45 occupy these locations in the their respective Tables) marked with thick Black border


In Table 1 Number 32 occupies the same row and column location as number 200 in Table 2 (Line 1 Col15)
the result of this is to be written to the "Summary Table - Part A" in the location under Column labelled P2 and the cell coloured green

Also in Table 1 the Number 45 occupies the same row and column location as number 309 in Table 2 (Line 6 Col3)
and as such will be written to the "Summary Table - Part A" under the Column labelled P5 as well as having the cell coloured green

The criteria for where this data is to be written to in the "Summary Table - Part A" under the column headings of P1 TO P7 is determined as
follows:


In Table 1 reading from left to right all the numbers coloured in yellow and in a horizontal direction we have the following sequence:

P1 P2 P3 P4 P5 P6 P7
Offset Number 40, 32, 817, 66, 309, 103, 334

Therefore the number 32 is the second number(P2) reading in a horizontal direction from left to right and Number 309 is the fifth number(P5)
when read in the horizontal sequence

There are (2) additional columns labelled "Number of Matching Locations" and "Number of Translations" respectively

1. The column "Number of Matching Locations" adds up all the Positions marked in green and have been identified as a repeated row and column
location.

2. The column "Number of Translations" is the total of all BLUE coloured locations from Table 1 that have a value in their
respective cells.. (Not to count empty blue cells) this total also adds the total of all repeated row and columns that have a thick Black
Border, in this example the TOTAL count for all events is 6 ( 4 blue + 2 yellow with thick black borders)


Task 5.

In this task in the Summary sheet I have created a Table called "Summary Table - Table 1 Current offset Positions", this Table simply records the numbers from Table 1 of the MAIN sheet
that have been coloured yellow against the Offset numbers located in Row 1 Column D to Column J, please see Figure 4

LH_Table Analysis.xlsm
	B	C	D	E	F	G	H	I
17			Summary Table - Table 1 Current offset Positions
18
19		P1	P2	P3	P4	P5	P6	P7
20	Lvl0	40	32	817	66	309	103	334
21	Lvl1
22	Lvl2
23	Lvl3
24	Lvl4
25	Lvl5
26	Lvl6
27	Lvl7
28	Lvl8
29	Lvl9
30	Lvl10
Summary

***IMPORTANT***


I would like all of the above to be a REPEATABLE process in that the above analysis can be performed on a set of different number data that
can be populated into both Table 1 and Table 2 of the "MAIN" sheet

1. What I have detailed above is what I call a Lvl0 run and acorrdingly all data written to their respective locations,

I am looking to run this an additional 10 times all up 11 runs.


So for each additional run I would like data to be appended to their resepctive RUN locations from Lvl0 to lvl 10.

I would like the Translations Tables "Translations From and To Table " in the Summary sheet to append output created in a left to right direction as per
Figure 5.


I look forward with in hearing back from someone soon



Regards

 

[Rijnsent]

Hi there,
that is quite an extensive description, although for someone that's not into your subject/sheet sometimes hard to follow. What I'm wondering: it sounds like you're looking for someone to build/code you a solution, am I right? If so: this is a forum with volunteers, if you're looking for someone to hire: from the forum rules: If you have an urgent need, check the Consulting Services page.
Cheers,
Koen