SOLVED

Compare 2 lists of data and identify a cell that meets certain criteria

Copper Contributor

Below I have 2 lists and you will see a few lines are on both list but there are a few that are on 1 but not the other.

 

If I am looking on my SCH List and see an invoice on this list that is over $1,000 and is not on my Session list I need to identify it. If you look at the SCH List you will see CT Corporation invoice # 23256391-RI but that invoice isn’t on my Session list. So I need a way to identify it. Does this help explain what I am trying to do ? Again thank you so much for your time and help with this.

 

Session List:

Vendor ID

Vendor Name

Invoice #

Date

Invoice Amount

12873

CSC

81039848

4/9/2022

$164.40

10868

CT Corporation

23861767-RI

4/14/2022

$313.70

27398

Quill Archivists

186

3/31/2022

$17.60

19850

Milton A. Policzer

322

3/1/2022

$935.50

 

SCH List:

Invoice Number

Vendor

Vendor name

Sep. Check?

Pmt. Amount

23256391-RI

10868

CT Corporation

No

$1,467.43

80326081

12873

CSC

No

$159.60

81039848

12873

CSC

No

$164.40

322

19850

Milton A. Policzer

No

$935.50

186

27398

Quill Archivists

No

$17.60

6 Replies

@psaavedra3 

Maybe with conditional formatting. In the attached file the invoice numbers in the SCH list are highlighted if the amount is above 1000 and the invoice number is missing in the SESSION list. 

@OliverScheurich Thank you for the quick response. I have attempted to the formula but its not working. The below line is on my SCH list and is not on my Session list so the formula should return and "Above 100 but not on Session" but unfortunately its not giving me anything. 

 

psaavedra3_0-1652203075782.png

 

Here is the formula i am using:

=IF(D40<=1000,"",IF(AND(D40>1000,NOT(ISNA(MATCH(C40,Sessions!$A$3:$A$1219,0)))),"","above 1000 and not in SESSION list"))

 

Any additional help would be greatly appreciated. 

@psaavedra3 

Can you upload a file which shows where the data is entered in your sheets? For example in the attached .pdf file you can see where the data is entered.

 

The most convenient way would be if you can enter sample data in the .xlsx Excel file however.

Sure! Attached you will see an example excel work book. 2 tabs, 1 for ACH SCH and 1 for Session. Some items are on both, tabs, some are not, I need to identify the items on the ACH SCH list that are over 1,000 and not on the session tab.
It didnt allow me to upload the file i created
best response confirmed by psaavedra3 (Copper Contributor)
Solution

@psaavedra3 

=IF(AND(D40>1000,ISNA(MATCH(C40,Sessions!$A$3:$A$1219,0))),"above 1000 and not in Sessions","")

You can try this formula. It's shorter than the original formula and it's adapted to the layout of your data. 

1 best response

Accepted Solutions
best response confirmed by psaavedra3 (Copper Contributor)
Solution

@psaavedra3 

=IF(AND(D40>1000,ISNA(MATCH(C40,Sessions!$A$3:$A$1219,0))),"above 1000 and not in Sessions","")

You can try this formula. It's shorter than the original formula and it's adapted to the layout of your data. 

View solution in original post