Old Guy Really Needs Help.

[zendog1960]

I have 2 Lists of Numbers ranging from 0 through 36. No numbers will be above 36. The numbers in E2:E7 are in Category A while the numbers in F2:F15 are in Category B. Any number not in those lists would be in Category C.
I input the numbers down column A with the Categories in columns B,C, and D. That's the way they want it. As I input the numbers I would like an "X" to be place in the appropriate column.

I can do this with formulas but there could be hundreds of numbers going down in column A. As you know, the more formulas there are the larger the file size.... ugggg

So anyway, I can do some beginner stuff in VBA but this has my head in a knot. Is there a sub I can use to make this happen or a custom function?
Any help with this would be super appreciated.

Being retired and getting older, I fear I have forgotten for more than I know about vba any more. .

So in a nutshell.....

HELP!!!

Testing.xlsm
	A	B	C	D	E	F
1	No.	A	B	C	A	B
2	2		X		8	0
3					11	1
4					17	2
5					20	4
6					26	9
7					29	12
8						13
9						16
10						21
11						24
12						25
13						28
14						33
15						36
Helper

 

[KRice]

What version of Excel are you using? You can update your profile to show that.

 

[KRice]

There are a lot of ways this could be done, but your Excel version will determine which one(s) are viable. Here is one idea that uses three formulas (entered only one time in the row 2 cells) to spill all of the results down each column...but this assumes you have Excel 365/2021. If not, then you would have the inconvenience of copying formulas down/across the results range, or rely on a VBA solution, as you've suggested.

MrExcel_20240107 (version 1).xlsx
	A	B	C	D	E	F
1	No.	A	B	C	A	B
2	2		X		8	0
3	26	X			11	1
4	9		X		17	2
5	33		X		20	4
6	34			X	26	9
7	27			X	29	12
8	20	X				13
9	8	X				16
10	7			X		21
11	0		X			24
12	34			X		25
13	17	X				28
14						33
15						36
Sheet4

Cell Formulas
Range	Formula
B2:B13	B2	=IF(ISNUMBER(MATCH(A2:INDEX(A:A,COUNTA(A:A)),$E$2:$E$7,0)),"X","")
C2:C13	C2	=IF(ISNUMBER(MATCH(A2:INDEX(A:A,COUNTA(A:A)),$F$2:$F$15,0)),"X","")
D2:D13	D2	=IF(B2#&C2#="","X","")
Dynamic array formulas.

 

[zendog1960]

There are a lot of ways this could be done, but your Excel version will determine which one(s) are viable. Here is one idea that uses three formulas (entered only one time in the row 2 cells) to spill all of the results down each column...but this assumes you have Excel 365/2021. If not, then you would have the inconvenience of copying formulas down/across the results range, or rely on a VBA solution, as you've suggested.

MrExcel_20240107 (version 1).xlsx
	A	B	C	D	E	F
1	No.	A	B	C	A	B
2	2		X		8	0
3	26	X			11	1
4	9		X		17	2
5	33		X		20	4
6	34			X	26	9
7	27			X	29	12
8	20	X				13
9	8	X				16
10	7			X		21
11	0		X			24
12	34			X		25
13	17	X				28
14						33
15						36
Sheet4

Cell Formulas
Range	Formula
B2:B13	B2	=IF(ISNUMBER(MATCH(A2:INDEX(A:A,COUNTA(A:A)),$E$2:$E$7,0)),"X","")
C2:C13	C2	=IF(ISNUMBER(MATCH(A2:INDEX(A:A,COUNTA(A:A)),$F$2:$F$15,0)),"X","")
D2:D13	D2	=IF(B2#&C2#="","X","")
Dynamic array formulas.

KRice, I appreciate that. At the moment I only have 2019 Excel but I am hoping to get my wife's 365 upgraded to the family plan really soon. Until then I will continue to search for a vba solution.

Thanks again!

 

[KRice]

If you don't want to deal with copying formula(s) throughout your table to generate the results, then there is one more option that hasn't been mentioned.

1. Above, I've relied on the "spill" feature in Excel 365/2021 so that a single formula can spill an entire array...in the example I've presented, each array for A,B,C spills down the column.
2. VBA is certainly an option.
3. And another option: convert your range to an official Excel table (click on a cell in the range, hit Ctrl-t to open a dialog window, confirm the range of the table is covered (A1:F15 here), and indicate that you have headers, then OK). Then the single formulas at the top of each column will automatically be applied down each column...a feature of official tables. To do this, the formulas need to be revised to non-spilling versions, as official tables do not allow spilled results. The example below shows this idea applied, and it relies on structured references (where the column heading names are used):

MrExcel_20240107 (version 1).xlsx
	A	B	C	D	E	F
1	No.	A	B	C	A Cat	B Cat
2	2		X		8	0
3	26	X			11	1
4	9		X		17	2
5	33		X		20	4
6	34			X	26	9
7	27			X	29	12
8	20	X				13
9	8	X				16
10	7			X		21
11	0		X			24
12	21		X			25
13						28
14						33
15						36
Sheet6

Cell Formulas
Range	Formula
B2:B15	B2	=IF(AND(ISNUMBER([@[No.]]),COUNTIF([A Cat],[@[No.]])>0),"X","")
C2:C15	C2	=IF(AND(ISNUMBER([@[No.]]),COUNTIF([B Cat],[@[No.]])>0),"X","")
D2:D15	D2	=IF(AND(ISNUMBER([@[No.]]),[@A]&[@B]=""),"X","")

 

[zendog1960]

If you don't want to deal with copying formula(s) throughout your table to generate the results, then there is one more option that hasn't been mentioned.

1. Above, I've relied on the "spill" feature in Excel 365/2021 so that a single formula can spill an entire array...in the example I've presented, each array for A,B,C spills down the column.
2. VBA is certainly an option.
3. And another option: convert your range to an official Excel table (click on a cell in the range, hit Ctrl-t to open a dialog window, confirm the range of the table is covered (A1:F15 here), and indicate that you have headers, then OK). Then the single formulas at the top of each column will automatically be applied down each column...a feature of official tables. To do this, the formulas need to be revised to non-spilling versions, as official tables do not allow spilled results. The example below shows this idea applied, and it relies on structured references (where the column heading names are used):

MrExcel_20240107 (version 1).xlsx
	A	B	C	D	E	F
1	No.	A	B	C	A Cat	B Cat
2	2		X		8	0
3	26	X			11	1
4	9		X		17	2
5	33		X		20	4
6	34			X	26	9
7	27			X	29	12
8	20	X				13
9	8	X				16
10	7			X		21
11	0		X			24
12	21		X			25
13						28
14						33
15						36
Sheet6

Cell Formulas
Range	Formula
B2:B15	B2	=IF(AND(ISNUMBER([@[No.]]),COUNTIF([A Cat],[@[No.]])>0),"X","")
C2:C15	C2	=IF(AND(ISNUMBER([@[No.]]),COUNTIF([B Cat],[@[No.]])>0),"X","")
D2:D15	D2	=IF(AND(ISNUMBER([@[No.]]),[@A]&[@B]=""),"X","")

Now that works for me. I have other thing in the spreadsheet i may need a bit of help with as they develop but for now. I am going to apply this to my testing sheet and study the formulas to fully understand what they do and how it apples to this application. My hope here is to learn so I can apply these principles to other spreadsheet where this would be of use.

Thank you so much.

 

[KRice]

You're welcome...I'm happy to help. Two more things:

1. Two of the formulas can be slightly shortened because the COUNTIF function will return a number, and any number >0 means that at least one number in the referenced column matches the number in the "No." column being evaluated, but when used for a logical test (like IF, OR, AND), any non-zero is automatically treated as TRUE, so the >0 parts of the formulas are superfluous.
2. You may have noticed that I changed the column headings for the reference lists of numbers. Official tables can't use duplicate heading names, so I renamed those for the A Category and B Category.

MrExcel_20240107 (version 1).xlsx
	A	B	C	D	E	F
1	No.	A	B	C	A Cat	B Cat
2	2		X		8	0
Sheet6

Cell Formulas
Range	Formula
B2	B2	=IF(AND(ISNUMBER([@[No.]]),COUNTIF([A Cat],[@[No.]])),"X","")
C2	C2	=IF(AND(ISNUMBER([@[No.]]),COUNTIF([B Cat],[@[No.]])),"X","")
D2	D2	=IF(AND(ISNUMBER([@[No.]]),[@A]&[@B]=""),"X","")

 

[zendog1960]

You're welcome...I'm happy to help. Two more things:

1. Two of the formulas can be slightly shortened because the COUNTIF function will return a number, and any number >0 means that at least one number in the referenced column matches the number in the "No." column being evaluated, but when used for a logical test (like IF, OR, AND), any non-zero is automatically treated as TRUE, so the >0 parts of the formulas are superfluous.
2. You may have noticed that I changed the column headings for the reference lists of numbers. Official tables can't use duplicate heading names, so I renamed those for the A Category and B Category.

MrExcel_20240107 (version 1).xlsx
	A	B	C	D	E	F
1	No.	A	B	C	A Cat	B Cat
2	2		X		8	0
Sheet6

Cell Formulas
Range	Formula
B2	B2	=IF(AND(ISNUMBER([@[No.]]),COUNTIF([A Cat],[@[No.]])),"X","")
C2	C2	=IF(AND(ISNUMBER([@[No.]]),COUNTIF([B Cat],[@[No.]])),"X","")
D2	D2	=IF(AND(ISNUMBER([@[No.]]),[@A]&[@B]=""),"X","")

This boards new slogan - "Making Code More Eloquent One Character At A Time"

Thank you.

 

[KRice]

Glad to help...