Taking occurrences of unique numbers (two tables) and placing results list across rows with corresponding occurrence values [Compare Two Tables, look

[ProgramUser]

Dear Excellers,

This is part of a larger project involving trigonometric ratios.

The Situation;
I have two tables, one table (A22:E22) with unique numbers derived from trigonometric functions, I then check this table’ values for duplicates using a vba code snippet I wrote (see below) into a corresponding table at (G3:K22), note: first line at row G2:K2 (from A2:E2) does not show results deliberately.

Table 1.

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
1	TRIG1	TRIG2	TRIG3	TRIG4	TRIG5		OCC1	OCC2	OCC3	OCC4	OCC5
2	13609	19421	26327	17265	26265
3	26265	18082	24118	18300	21930		0	0	0	0	0
4	27387	14866	14211	28465	28075		0	0	0	0	0
5	18082	29988	14047	12464	24202		1	0	0	0	0
6	15507	25278	25354	23189	17004		0	0	0	0	0
7	21287	29805	28016	27830	13939		0	0	0	0	0
8	12280	21631	17004	15655	19051		0	0	1	0	0
9	15006	22452	24651	16058	21407		0	0	0	0	0
10	25549	17315	29968	28485	19458		0	0	0	0	0
11	27772	23464	14652	20750	16392		0	0	0	0	0
12	24352	16378	23869	22078	14306		0	0	0	0	0
13	18927	28195	26059	29542	19484		0	0	0	0	0
14	17587	25518	19917	19370	28605		0	0	0	0	0
15	22951	16813	19239	20886	13011		0	0	0	0	0
16	20452	14951	26168	19273	16522		0	0	0	0	0
17	29794	23468	22374	21287	19033		0	0	0	9	0
18	26575	24139	24857	25049	17914		0	0	0	0	0
19	27588	13420	17720	16788	25928		0	0	0	0	0
20	13082	20248	19421	24558	23934		0	0	17	0	0
21	18343	27737	12536	28217	21333		0	0	0	0	0
22	28015	16705	16473	17168	22086		0	0	0	0	0
Test of Index Lookup

.....I need to then copy or rather populate the quantity of duplicate records (as evidenced from G3:K22) into another row (say…transposed across rows) with the TRIG Functions at top with the corresponding occurrence value below it, like this;

Table 2 (sample).

Excel 2012
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	OCC1	OCC2	OCC3	OCC4	OCC5
2
3	0	0	0	0	0
4	0	0	0	0	0
5	1	0	0	0	0
6	0	0	0	0	0
7	0	0	0	0	0
8	0	0	1	0	0
9	0	0	0	0	0
10	0	0	0	0	0
11	0	0	0	0	0		13609	26462	27387	12059	15507	21287	12280	15006	25549
12	0	0	0	0	0		NotMatch	NotMatch	#N/A	NotMatch	NotMatch	NotMatch	NotMatch	NotMatch	NotMatch
13	0	0	0	0	0
14	0	0	0	0	0
15	0	0	0	0	0
16	0	0	0	0	0
17	0	0	0	9	0
18	0	0	0	0	0
19	0	0	0	0	0
20	0	0	17	0	0
21	0	0	0	0	0
22	0	0	0	0	0
Test of Index Lookup

Cell Formulas
Range	Formula
M12	{=IF($G3=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
N12	{=IF($G4=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
O12	{=IF($G5=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
P12	{=IF($G6=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
Q12	{=IF($G7=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
R12	{=IF($G8=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
S12	{=IF($G9=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
T12	{=IF($G10=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
U12	{=IF($G11=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

....Note that matches do occur at G5, and I8, and I20, and J17….therefore;
I could just populate say 20 columns across with the TRIG Numbers, which would then mean that I would need multiple rows dependent on the amount of TRIG functions I am testing…..like this;

Table 3.

Excel 2012
	I	J	K	L	M	N	O	P	Q	R	S
1	OCC3	OCC4	OCC5
2
3	0	0	0
4	0	0	0
5	0	0	0
6	0	0	0
7	0	0	0
8	1	0	0
9	0	0	0
10	0	0	0
11	0	0	0		13609	26462	27387	12059	15507	21287	12280
12	0	0	0		NotMatch	NotMatch	#N/A	NotMatch	NotMatch	NotMatch	NotMatch
13	0	0	0		19421	18082	14866	29988	25278	29805	21631
14	0	0	0		Result	Result	Result		.	..
15	0	0	0		26327	24118	14211	14047	25354	28016	17004
16	0	0	0		Result	Result	Result		.	..
17	0	9	0
18	0	0	0
19	0	0	0
20	17	0	0
21	0	0	0
22	0	0	0
Test of Index Lookup

Cell Formulas
Range	Formula
M12	{=IF($G3=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
N12	{=IF($G4=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
O12	{=IF($G5=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
P12	{=IF($G6=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
Q12	{=IF($G7=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
R12	{=IF($G8=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
S12	{=IF($G9=0,"NotMatch",(INDEX($G$3:$K$22,MATCH($A2,$A$2:$E$22,0)*ROW($A2),ROW($A2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

VBA Code for checking occurences;

' /////////////
' Trig Digits////
' /////////////
' Code by ProgramUser Jan 2016


Public Sub Count_Match5_TickTape1a()
    Dim myRange As Range
    Dim c As Range
    
    ' ######
    'Speed up code execution
    ' ######
    'Get current state of various Excel settings;


    screenUpdateState = Application.ScreenUpdating
    statusBarState = Application.DisplayStatusBar
    calcState = Application.Calculation
    eventsState = Application.EnableEvents
    displayPageBreakState = ActiveSheet.DisplayPageBreaks 'sheet-level setting only


    'Switch off Excel functionality so code runs faster
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False 'sheet-level setting only
    
    '  ####
    '  Basic Looping starts
    '  ####
    Set myRange = Range("A2", Range("E1048576").End(xlUp))
    
    For Each c In myRange
        For i = c.Row - 2 To 1 Step -1
            If Not IsError(Application.Match(c, myRange.Rows(i), 0)) Then
            ' so I can remember stuff
            ' Syntax for .Offset = (example) Range.Offset(RowOffset, ColumnOffset)
            ' Omitting the first number in the Offset reference below, i.e. (2,5) ""(,5)"" means we are only concerned with the column and not the row
                c.Offset(, 6) = c.Row - 2 - i
                Exit For
            Else
                c.Offset(, 6) = 0
            End If
        Next i
    Next c
        'after my code completes, restore previous state;


    Application.ScreenUpdating = screenUpdateState
    Application.DisplayStatusBar = statusBarState
    Application.Calculation = calcState
    Application.EnableEvents = eventsState
    ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'sheet-level setting only
End Sub

My Problem
I have tried various iterations of INDEX/MATCH using ROW to show these matches but without any luck. Additionally, I could just use copy/paste function from within VBA, I don’t mind. Does anyone have any suggestions? A VBA option would be nice….

Regards,
ProgramUser

 

[ProgramUser]

Hey everyone,

Sorry, I forgot to clarify, my VBA code above works just fine however, it is just the correct placement of the occurrences that I am after in column M through to column AF as per above examples.

Cheers, and looking forward to any advice you may have.

Regards,

ProgramUser

 

[ProgramUser]

[Bump] - Pretty pleaseeeee......

Anyone?

PU.