Old Guy Really Needs Help.

zendog1960

Active Member
Joined
Sep 27, 2003
Messages
459
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEF
1No.ABCAB
22X80
3111
4172
5204
6269
72912
813
916
1021
1124
1225
1328
1433
1536
Helper
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
What version of Excel are you using? You can update your profile to show that.
 
Upvote 0
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
ABCDEF
1No.ABCAB
22 X 80
326X111
49X172
533X204
634X269
727X2912
820X13
98X16
107X21
110X24
1234X25
1317X28
1433
1536
Sheet4
Cell Formulas
RangeFormula
B2:B13B2=IF(ISNUMBER(MATCH(A2:INDEX(A:A,COUNTA(A:A)),$E$2:$E$7,0)),"X","")
C2:C13C2=IF(ISNUMBER(MATCH(A2:INDEX(A:A,COUNTA(A:A)),$F$2:$F$15,0)),"X","")
D2:D13D2=IF(B2#&C2#="","X","")
Dynamic array formulas.
 
Upvote 0
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
ABCDEF
1No.ABCAB
22 X 80
326X111
49X172
533X204
634X269
727X2912
820X13
98X16
107X21
110X24
1234X25
1317X28
1433
1536
Sheet4
Cell Formulas
RangeFormula
B2:B13B2=IF(ISNUMBER(MATCH(A2:INDEX(A:A,COUNTA(A:A)),$E$2:$E$7,0)),"X","")
C2:C13C2=IF(ISNUMBER(MATCH(A2:INDEX(A:A,COUNTA(A:A)),$F$2:$F$15,0)),"X","")
D2:D13D2=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!
 
Upvote 0
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
ABCDEF
1No.ABCA CatB Cat
22 X 80
326X  111
49 X 172
533 X 204
634  X269
727  X2912
820X  13
98X  16
107  X21
110 X 24
1221 X 25
13   28
14   33
15   36
Sheet6
Cell Formulas
RangeFormula
B2:B15B2=IF(AND(ISNUMBER([@[No.]]),COUNTIF([A Cat],[@[No.]])>0),"X","")
C2:C15C2=IF(AND(ISNUMBER([@[No.]]),COUNTIF([B Cat],[@[No.]])>0),"X","")
D2:D15D2=IF(AND(ISNUMBER([@[No.]]),[@A]&[@B]=""),"X","")
 
Upvote 1
Solution
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
ABCDEF
1No.ABCA CatB Cat
22 X 80
326X  111
49 X 172
533 X 204
634  X269
727  X2912
820X  13
98X  16
107  X21
110 X 24
1221 X 25
13   28
14   33
15   36
Sheet6
Cell Formulas
RangeFormula
B2:B15B2=IF(AND(ISNUMBER([@[No.]]),COUNTIF([A Cat],[@[No.]])>0),"X","")
C2:C15C2=IF(AND(ISNUMBER([@[No.]]),COUNTIF([B Cat],[@[No.]])>0),"X","")
D2:D15D2=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.
 
Upvote 0
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
ABCDEF
1No.ABCA CatB Cat
22 X 80
Sheet6
Cell Formulas
RangeFormula
B2B2=IF(AND(ISNUMBER([@[No.]]),COUNTIF([A Cat],[@[No.]])),"X","")
C2C2=IF(AND(ISNUMBER([@[No.]]),COUNTIF([B Cat],[@[No.]])),"X","")
D2D2=IF(AND(ISNUMBER([@[No.]]),[@A]&[@B]=""),"X","")
 
Upvote 0
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
ABCDEF
1No.ABCA CatB Cat
22 X 80
Sheet6
Cell Formulas
RangeFormula
B2B2=IF(AND(ISNUMBER([@[No.]]),COUNTIF([A Cat],[@[No.]])),"X","")
C2C2=IF(AND(ISNUMBER([@[No.]]),COUNTIF([B Cat],[@[No.]])),"X","")
D2D2=IF(AND(ISNUMBER([@[No.]]),[@A]&[@B]=""),"X","")
This boards new slogan - "Making Code More Eloquent One Character At A Time"

Thank you.
 
Upvote 0

Forum statistics

Threads
1,223,165
Messages
6,170,449
Members
452,327
Latest member
kris9926

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
Back
Top