ISNUMBER Multiple Criteria within Cells

copyboy007

Board Regular
Joined
May 17, 2005
Messages
60
Hi, all. I'm trying to categorize some raw data (a list of invoices) that consecutively populated just Column A and am trying to identify certain rows using IF and ISNUMBER functions meeting multiple criteria because. Some of them have extra information, so I'm hopeful to figure a way to identify all the rows that refer to those invoices while avoiding other invoices.

For example, a regular entry can show as:

4/17/20 $5.00
123456

One with extra information can show as:

SubCompany A (ABC) $6
SubCompany B (DEF) $4
Total Sub Cost for Invoice: $10
4/17/20 $25.00
789012

So far, I have one formula that looks for one criteria within the cell to put 'XXX' in the adjacent column, which works for SubCompany rows.

=IF(ISNUMBER(SEARCH(") $",A1)),"XXX","---")

I've tried using OR to add more criteria to include the other rows, but no luck, so far without getting errors. The ideal result is:

4/17/20 $5.00
123456
SubCompany A (ABC) $6 XXX
SubCompany B (DEF) $4 XXX
Total Sub Cost for Invoice: $10 XXX
4/17/20 $25.00 XXX
789012 XXX
4/17/20 $5.00
345678

From there, I'll filter using those XXXs, then figure what to do next in making the relevant data more organized. Might have to extract further.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,921
Office Version
  1. 365
Platform
  1. Windows
This should work for the rows with $ amounts, what is the criteria for 789012 though, why is that included but not 345678?

=IF(OR(ISNUMBER(SEARCH({") $",": $"},A2))),"XXX","---")
 

copyboy007

Board Regular
Joined
May 17, 2005
Messages
60
This should work for the rows with $ amounts, what is the criteria for 789012 though, why is that included but not 345678?

=IF(OR(ISNUMBER(SEARCH({") $",": $"},A2))),"XXX","---")

Thank you. The formula works for the rows with Sub information.

123456 and 345678 are invoices that do not have the extra Sub information.
789012 has extra rows with Sub information, so those would get labeled.

The two rows with date / amount and invoice number after the rows with Sub information would be good to label, too, if there's some way to identify them.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,781
Messages
5,655,253
Members
418,183
Latest member
skaufman

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
Top