Repeating Numbers in Tabled Data

[cotech_10]

Hi All,


I have created a Table that has 11 Columns and 18 Rows, contained in this Table is number data in each cell. What I would like to do is to create a macro that can calculate
and display the number of repeated numbers in any given grouping of lines and produce the results to the right of the Tabled Data as per Attachment 1.

The macro would require input for any line combinations

I have given (3) such examples to the right of this Table:

1. In this first example I have selected Lines 1 to 3 of this table for analysis.

2. In the second example I have selected Lines 3 to 7 for analysis

3. In the third example I have selected two non consecutive lines being
Lines 8 and 17.


As mentioned the "output data" can be written to the right of the Table as per the attachment with automatic calculations on the following:



1. "No of Repeating Numbers" = This total is simply the total of all numbers appearing in the given line

So for this analysis there are 9 Numbers that have repeated in the line grouping "Lines 1 to 3"

2. "No of offset numbers" = This is the total of the repeated numbers in this line that appear in the "Offsets" Line at E2..K2

If we refer to Lines 1 to 3 analysis out of the 9 repeating numbers there are (4)numbers that appear in the "Offset" range located at E2..K2. they are 45,100,103 & 108

These numbers can be colour coded as per the attachment output.



I look forward in hearing back from someone soon.



Kind Regards

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Also, thanks for using XL2BB - makes it much easier.

 

[cotech_10]

Peter,

Spoke abit too soon... lol. I just discovered that if you put a single value in Column N the calculations for
2. The Number of Offset numbers do not seem to have calculated properly. Please see attached

In the example I have provided I had specified single values in cells N13, N14 & N15 those being respectively Lines 1,2,3
I have highlighted those lines in Green.

Line 3 shows 240 as being a OFFSET number and has accordingly tallied.

PMacros.xlsm
	A	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
1
2			Offsets:		340	103	63	108	45	392	100
3
4
5		Col 1	Col 2	Col 3	Col 4	Col 5	Col 6	Col 7	Col 8	Col 9	Col 10	Col 11		Output Analysis :		No of Repeating Numbers	No of offset numbers	Output Data
6	Line No:
7	1	1	5	10	15	108	25	106	35	35	45	50		1-3		9	4	10	15	35	45	100	103	106	108	240
8	2	43	6	100	101	102	103	104	105	106	45	108		3-7		18	2	6	7	8	9	10	11	12	14	15	17	21	33	40	99	100	102	103	240
9	3	99	240	240	8	9	10	100	12	103	14	15		8,17		5	3	103	108	231	392	454
10	4	6	8	34	99	7	21	11	40	102	105	17		1,3,5,7,9,11,13,15,17		27	3	1	5	6	7	8	9	10	11	12	13	14	15	17	21	33	35	40	48	52	100	102	103	105	106	108	240	454
11	5	5	9	36	100	6	7	6	8	8	33	33		17-18		3	1	22	108	454
12	6	43	10	38	101	11	103	15	48	106	7	8		2,4,6,8,10		14	3	6	7	8	11	15	43	99	100	101	102	103	105	106	392
13	7	21	11	40	102	11	12	13	14	15	16	17		1		1	0	35
14	8	100	800	392	103	102	103	231	392	106	392	99		2		0	0
15	9	101	13	44	104	6	7	6	8	8	33	33		3		0	1	240
16	10	102	14	46	105	3	6	9	12	15	18	105
17	11	103	15	48	106	7	21	11	40	102	105	17
18	12	104	16	50	107	16	17	18	19	20	21	22
19	13	105	17	52	108	13	105	17	52	108	2	3
20	14	106	18	54	109	190	191	190	192	14	106	3
21	15	107	19	56	110	11	103	15	48	106	7	1
22	16	108	20	58	111	6	7	6	8	8	33	33
23	17	109	108	60	112	231	232	234	454	466	108	454
24	18	110	22	62	113	16	17	18	19	20	21	22
25
26
Sheet1

Thanks and Regards

 

[Peter_SSs]

Spoke abit too soon... lol.

Try making this change near the end of the code & do some more tests.

.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
If d1.Count > 1 Then .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight

 

[cotech_10]

Hi Peter,

Once again just another amazing great effort and your fix solved that issue.
I am sure now that is the extent of what I intend to get out of this analysis and
very much appreciate your assistance.

The XL2BB add-in is just a great tool to use to provide data to you.. just easy to work with..

Thanking you once again..

 

[Peter_SSs]