In the formula instead of putting column reference can be used column numbers.

Kishan · Jun 23, 2021

Using Excel 2000

Hi,

In the columns C:H there are results which columns numbers are (3-4-5-6-7-8)

In the column O5:Y5 I have header with 4 unique Pattern

In the range O6:Y10 there is formula SUMPRODUCT that is populating result of header O5:Y5 header unique patterns of different "4 columns" for that i need to change formula for the column I want to be checked.

I need help is it possible that formulas are in the O6:Y10 can pick the column reference from the
Columns J:M in that way i do not need to change the formula just changing the numbers in the range J6:M10 I will get results.

Here below is a list of formula are used for the results.

VBA Code:

O6:Y6 =SUMPRODUCT(--($C$6:$C$5006&" | " &$D$6:$D$5006&" - "&$E$6:$E$5006&" | "&$F$6:$F$5006=O$5))
O7:Y7 =SUMPRODUCT(--($C$6:$C$5006&" | " &$D$6:$D$5006&" - "&$E$6:$E$5006&" | "&$G$6:$G$5006=O$5))
O8:Y8 =SUMPRODUCT(--($C$6:$C$5006&" | " &$D$6:$D$5006&" - "&$E$6:$E$5006&" | "&$H$6:$H$5006=O$5))
O9:Y9 =SUMPRODUCT(--($D$6:$D$5006&" | " &$E$6:$E$5006&" - "&$F$6:$F$5006&" | "&$G$6:$G$5006=O$5))
O10:Y10 =SUMPRODUCT(--($D$6:$D$5006&" | " &$E$6:$E$5006&" - "&$G$6:$G$5006&" | "&$H$6:$H$5006=O$5))

If I am not asking much does it is possible VBA solution that would be great assist

*	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
1
2
3
4			Col	Col	Col	Col	Col	Col		Column nº	Column nº	Column nº	Column nº		4 Patt	4 Patt	4 Patt	4 Patt	4 Patt	4 Patt	4 Patt	4 Patt	4 Patt	4 Patt	4 Patt
5			3	4	5	6	7	8		Column nº	Column nº	Column nº	Column nº		1 \| 1 - 1 \| 1	1 \| 1 - 1 \| X	1 \| 1 - X \| X	1 \| X - 1 \| 1	1 \| X - 1 \| 2	X \| 1 - X \| X	X \| 1 - 2 \| 2	X \| X - 2 \| 2	X \| 2 - X \| X	2 \| 1 - 2 \| X	2 \| X - 1 \| 2
6			1	1	1	1	2	1		3	4	5	6		6	6	0	4	0	3	0	11	0	14	4
7			2	1	2	X	X	X		3	4	5	7		5	3	0	1	0	2	0	2	0	3	2
8			X	X	2	2	2	1		3	4	5	8		8	3	0	2	0	1	0	4	0	4	1
9			2	X	1	2	2	1		4	5	6	7		3	0	3	0	0	0	2	0	0	0	0
10			X	1	X	X	1	1		4	5	7	8		3	1	1	1	0	2	0	0	0	0	0
11			2	X	1	2	2	X
12			2	1	2	X	1	1
13			2	1	2	X	1	X
14			1	1	1	X	X	X
15			2	1	2	X	1	1
16			X	X	2	2	1	2
17			1	1	1	1	2	1
18			2	X	1	2	1	X
19			X	1	X	X	X	1
20			2	1	2	X	2	1
21			2	1	2	X	2	1
22			X	X	2	2	1	X
23			X	X	2	2	1	2
24			1	1	1	1	1	1
25			2	1	2	X	X	1
26			X	X	2	2	1	2
27			1	X	1	1	X	X
28			X	X	2	2	1	2
29			2	1	2	X	2	1
30			1	1	1	1	1	1
31			X	X	2	2	1	1
32			1	1	1	X	2	1
33			1	X	1	1	X	1
34			X	X	2	2	1	1
35			2	1	2	X	2	X
36			2	1	2	X	1	1
37			1	1	1	X	1	1
38			X	1	X	X	X	X
39			2	1	2	X	2	1
40			1	1	1	X	X	1
41			2	1	2	X	1	X
42			1	1	1	X	X	1
43			2	1	2	X	X	1
44			X	X	2	2	1	1
45			1	1	1	X	1	2
46			2	X	1	2	X	2
47			1	X	1	1	1	1
48			X	X	2	2	2	1
49			2	1	2	X	2	1
50			1	1	1	1	1	X
51			X	X	2	2	1	X
52			1	X	1	1	X	X
53			1	1	1	1	2	X
54
55

Result image is attached

Thank you in advance

Regards,
Kishan

Marcelo Branco · Jun 23, 2021

Maybe...

O6
=SUMPRODUCT(--(INDEX($C$6:$H$5006,0,MATCH($J6,$C$5:$H$5,0))&" | " &INDEX($C$6:$H$5006,0,MATCH($K6,$C$5:$H$5,0))&" - "&INDEX($C$6:$H$5006,0,MATCH($L6,$C$5:$H$5,0))&" | "&INDEX($C$6:$H$5006,0,MATCH($M6,$C$5:$H$5,0))=O$5))
copy across and down

Hope this helps

It would be easier if in J:M there was the relative position of the columns, that is, for example in
J6:M6
1 2 3 4
in J7:M7
1 2 3 5
etc

M.

Kishan · Jun 23, 2021

Marcelo Branco said:
Maybe...

O6
=SUMPRODUCT(--(INDEX($C$6:$H$5006,0,MATCH($J6,$C$5:$H$5,0))&" | " &INDEX($C$6:$H$5006,0,MATCH($K6,$C$5:$H$5,0))&" - "&INDEX($C$6:$H$5006,0,MATCH($L6,$C$5:$H$5,0))&" | "&INDEX($C$6:$H$5006,0,MATCH($M6,$C$5:$H$5,0))=O$5))
copy across and down

Hope this helps

It would be easier if in J:M there was the relative position of the columns, that is, for example in
J6:M6
1 2 3 4
in J7:M7
1 2 3 5
etc

M.

Marcelo Branco, your given formula worked as request, it was not problem I changed as per you suggestion column numbers it is easier for me too indeed. Here below is attached a new changed layout.

I have 14 columns and 81 patterns across and 1001 rows to fill down total 81081 cells to fill the formula so it is going to be a very slow procedure might it crash and excel does not response my request is there and VBA solution that can result in values only.

Please help if it could be done. As per present layout and than I will extend the ranges and will adjust it as per my need.

Thank you very much for your kind help.

*	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
1
2
3
4			Col	Col	Col	Col	Col	Col		Column nº	Column nº	Column nº	Column nº		4 Patt	4 Patt	4 Patt	4 Patt	4 Patt	4 Patt	4 Patt	4 Patt	4 Patt	4 Patt	4 Patt
5			1	2	3	4	5	6		Column nº	Column nº	Column nº	Column nº		1 \| 1 - 1 \| 1	1 \| 1 - 1 \| X	1 \| 1 - X \| X	1 \| X - 1 \| 1	1 \| X - 1 \| 2	X \| 1 - X \| X	X \| 1 - 2 \| 2	X \| X - 2 \| 2	X \| 2 - X \| X	2 \| 1 - 2 \| X	2 \| X - 1 \| 2
6			1	1	1	1	2	1		1	2	3	4		6	6	0	4	0	3	0	11	0	14	4
7			2	1	2	X	X	X		1	2	3	5		5	3	0	1	0	2	0	2	0	3	2
8			X	X	2	2	2	1		1	2	3	6		8	3	0	2	0	1	0	4	0	4	1
9			2	X	1	2	2	1		2	3	4	5		3	0	3	0	0	0	2	0	0	0	0
10			X	1	X	X	1	1		2	3	5	6		3	1	1	1	0	2	0	0	0	0	0
11			2	X	1	2	2	X
12			2	1	2	X	1	1
13			2	1	2	X	1	X
14			1	1	1	X	X	X
15			2	1	2	X	1	1
16			X	X	2	2	1	2
17			1	1	1	1	2	1
18			2	X	1	2	1	X
19			X	1	X	X	X	1
20			2	1	2	X	2	1
21			2	1	2	X	2	1
22			X	X	2	2	1	X
23			X	X	2	2	1	2
24			1	1	1	1	1	1
25			2	1	2	X	X	1
26			X	X	2	2	1	2
27			1	X	1	1	X	X
28			X	X	2	2	1	2
29			2	1	2	X	2	1
30			1	1	1	1	1	1
31			X	X	2	2	1	1
32			1	1	1	X	2	1
33			1	X	1	1	X	1
34			X	X	2	2	1	1
35			2	1	2	X	2	X
36			2	1	2	X	1	1
37			1	1	1	X	1	1
38			X	1	X	X	X	X
39			2	1	2	X	2	1
40			1	1	1	X	X	1
41			2	1	2	X	1	X
42			1	1	1	X	X	1
43			2	1	2	X	X	1
44			X	X	2	2	1	1
45			1	1	1	X	1	2
46			2	X	1	2	X	2
47			1	X	1	1	1	1
48			X	X	2	2	2	1
49			2	1	2	X	2	1
50			1	1	1	1	1	X
51			X	X	2	2	1	X
52			1	X	1	1	X	X
53			1	1	1	1	2	X
54
55

Regards,
Kishan

Marcelo Branco · Jun 23, 2021

I'm busy now. Maybe someone else can help you with a macro.

M.

Marcelo Branco · Jun 24, 2021

See if the macro below does what you're looking for - it worked for me using the data sample in post #3
(relative positions in J:M)

VBA Code:

Sub aTest()
    Dim lNumRowsInd As Long, arrIndex(1 To 4) As Variant, arrStrs(1 To 11) As Variant
    Dim i As Long, j As Long, k As Long, n As Long, lRow As Long
    Dim spl As Variant, lCounter As Long
    
    'Initialize variables
    Dim vIndex As Variant
    vIndex = Range("J6:M" & Cells(Rows.Count, "J").End(xlUp).Row)
    Dim vStrings As Variant
    vStrings = Range("O5:Y5").Value
    For i = 1 To 11
        arrStrs(i) = Replace(Replace(vStrings(1, i), "-", "|"), " ", "")
    Next i
    lNumRowsInd = Cells(Rows.Count, "J").End(xlUp).Row
    Dim vResult As Variant
    vResult = Range("O6:Y" & lNumRowsInd).Value
    Dim vData As Variant
    vData = Range("C6:H" & Cells(Rows.Count, "C").End(xlUp).Row)
    
    'Loop
    For i = 1 To UBound(vResult, 1)
        For j = 1 To 4
            arrIndex(j) = vIndex(i, j)
        Next j
        For k = 1 To UBound(vResult, 2)
            spl = Split(arrStrs(k), "|")
            vResult(i, k) = 0
            For lRow = 1 To UBound(vData, 1)
                lCounter = 0
                For n = 1 To 4
                    If CStr(vData(lRow, arrIndex(n))) = CStr(spl(n - 1)) Then _
                        lCounter = lCounter + 1
                Next n
                If lCounter = 4 Then vResult(i, k) = vResult(i, k) + 1
            Next lRow
        Next k
    Next i
    'Transfer results
    Range("O6:Y" & lNumRowsInd) = vResult
End Sub

Hope this helps

M.

Kishan · Jun 25, 2021

Marcelo Branco said:

See if the macro below does what you're looking for - it worked for me using the data sample in post #3
(relative positions in J:M)

VBA Code:

Sub aTest()
    Dim lNumRowsInd As Long, arrIndex(1 To 4) As Variant, arrStrs(1 To 11) As Variant
    Dim i As Long, j As Long, k As Long, n As Long, lRow As Long
    Dim spl As Variant, lCounter As Long
   
    'Initialize variables
    Dim vIndex As Variant
    vIndex = Range("J6:M" & Cells(Rows.Count, "J").End(xlUp).Row)
    Dim vStrings As Variant
    vStrings = Range("O5:Y5").Value
    For i = 1 To 11
        arrStrs(i) = Replace(Replace(vStrings(1, i), "-", "|"), " ", "")
    Next i
    lNumRowsInd = Cells(Rows.Count, "J").End(xlUp).Row
    Dim vResult As Variant
    vResult = Range("O6:Y" & lNumRowsInd).Value
    Dim vData As Variant
    vData = Range("C6:H" & Cells(Rows.Count, "C").End(xlUp).Row)
   
    'Loop
    For i = 1 To UBound(vResult, 1)
        For j = 1 To 4
            arrIndex(j) = vIndex(i, j)
        Next j
        For k = 1 To UBound(vResult, 2)
            spl = Split(arrStrs(k), "|")
            vResult(i, k) = 0
            For lRow = 1 To UBound(vData, 1)
                lCounter = 0
                For n = 1 To 4
                    If CStr(vData(lRow, arrIndex(n))) = CStr(spl(n - 1)) Then _
                        lCounter = lCounter + 1
                Next n
                If lCounter = 4 Then vResult(i, k) = vResult(i, k) + 1
            Next lRow
        Next k
    Next i
    'Transfer results
    Range("O6:Y" & lNumRowsInd) = vResult
End Sub

Hope this helps

M.

Marcelo Branco, I am very gratefully to you for taking a time from your busy schedule and giving a macro solution for me as per my need. The code worked fine and it took 0.00:01 second as per using the data sample post #3.

Modified a code to work with my full data using in 14 columns, 81 patterns and 1001 rows populating a results across 1001*81 = 81081 cells it took only 00:05:38 minutes. The code you build it is so easy to understand and very simple to modify it is unbelievable and amazing! ?

I am very obliged to you for giving me a huge and great timesaver solution.

I wish for you to have a great weekend and Good Luck.

Kind Regards
Kishan

Marcelo Branco · Jun 25, 2021

Great, the code has worked for you. Glad to help.

Just for curiosity. How long did it take for your real case?
I didn't quite understand what 00:05:38 minutes means (?)

M.

Kishan · Jun 26, 2021

Marcelo Branco said:
Great, the code has worked for you. Glad to help.

M.

Marcelo Branco, I am grateful to you for that the code worked with my older version perfect and smoothly. ?

Marcelo Branco said:
Just for curiosity. How long did it take for your real case?
I didn't quite understand what 00:05:38 minutes means (?)

M.

As per sample post #3 that has 6 columns with 48 row data, columns to check 5 rows and header Patt 11 to populate 11*5 = 55 cells to populate which took time 01 second.

After modifying real data extending it to 6 to 14 columns with 48 to 2300+ rows + columns to check 5 rows to 1001 and + extending header 11 to 81 that mean 1001*81 = 81081 cells to populate with comprehensive data code took only time to finish 5 minutes and 38 seconds Without any problem at all. I am very happy with your code solution.

Good Luck to you and have a pleasant time.

Kind Regards
Kishan

In the formula instead of putting column reference can be used column numbers.

Kishan

Well-known Member

Attachments

Excel Facts

Marcelo Branco

MrExcel MVP

Kishan

Well-known Member

Attachments

Marcelo Branco

MrExcel MVP

Marcelo Branco

MrExcel MVP

Kishan

Well-known Member

Marcelo Branco

MrExcel MVP

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

In the formula instead of putting column reference can be used column numbers.

Well-known Member

Attachments

Excel Facts

MrExcel MVP

Well-known Member

Attachments

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock