Table Comparisons

cotech_10

Board Regular
Joined
Sep 11, 2010
Messages
102
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

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


LH_Table Analysis.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBA
1Offset #403281766309103334Table 1Offset #1210069552004599Table 2
2
3Col1Col2Col3Col4Col5Col6Col7Col8Col9Col10Col11Col12Col13Col14Col15Col16Col17Col18Col19Col20Col1Col2Col3Col4Col5Col6Col7Col8Col9Col10Col11Col12Col13Col14Col15Col16Col17Col18Col19Col20
4Line 14090483246157032Line 133126782589200
5Line 233960549666716Line 25055606988213434
6Line 3323699107999817Line 31799333
7Line 466545Line 4201210506550
8Line 577442208Line 5332
9Line 688128309421Line 611010045
10Line 7101129Line 7121131
11Line 8102148Line 814182
12Line 9103Line 977
13Line 10Line 1076
14Line 11Line 11
15Line 12110Line 12
16Line 13Line 1375
17Line 14232Line 14
18Line 15334Line 1566
19Line 16Line 1655
20Line 17Line 17
21Line 18Line 18
22Line 19Line 19
23Line 20Line 20
24Line 21Line 21
25Line 22Line 22
26Line 23Line 23
27Line 24Line 24
28Line 25Line 25
29Line 2699Line 26
30Line 27Line 27401
31Line 28Line 28
32Line 29Line 29
33Line 30Line 30
34Line 31Line 31
35Line 32Line 32
36Line 33Line 33
37Line 34Line 34
38Line 35Line 35
39Line 36Line 36
40Line 37Line 37
41Line 38Line 38
42Line 39Line 39
43Line 40Line 40
44Line 41Line 41
45Line 42Line 42
46Line 43Line 43
47Line 44Line 44
48Line 45Line 45
49Line 46Line 46
50Line 47Line 47
51Line 48Line 48
52Line 49Line 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
BCDEFGHIJKL
1Summary Table - Part A
2Number of Matching LocationsNumber of Translations
3P1P2P3P4P5P6P7
4Lvl03230926
5Lvl1
6Lvl2
7Lvl3
8Lvl4
9Lvl5
10Lvl6
11Lvl7
12Lvl8
13Lvl9
14Lvl10
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
NOPQRSTU
2Translations From and To TableTranslations From and To Table
3FromToFromTo
4Lvl 01290Lvl 1etcetc
5100128etcetc
696666etcetc
7550etcetc
8200n/aetcetc
945n/aetcetc
1099699etcetc
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
BCDEFGHI
17Summary Table - Table 1 Current offset Positions
18
19P1P2P3P4P5P6P7
20Lvl0403281766309103334
21Lvl1
22Lvl2
23Lvl3
24Lvl4
25Lvl5
26Lvl6
27Lvl7
28Lvl8
29Lvl9
30Lvl10
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,299
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,217
Messages
5,594,887
Members
413,947
Latest member
gizmolucy

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