Paid vs Unpaid

**ems.payroll** · 08-22-2008, 09:59 AM

I have two excel sheets. The first lists all orders submitted. The second lists all orders paid, or pending payment. I would like a column in the first sheet that would show if that order was paid, by pulling the info from the second sheet.

**Richard Schollar** · 08-22-2008, 10:03 AM

Hi

Assuming: Orders sheet column A contains orders submitted, Paid sheet column A lists orders paid/pending then in Orders sheet in an adjacent column (eg col B) use this and copy down as many orders as you have:

=IF(ISNUMBER(MATCH(Orders!A2,Paid!A:A,0)),"Paid","Outstanding")

You can change "Paid" and "Outstanding" to suit.

Richard

**ems.payroll** · 08-22-2008, 10:07 AM

The paid column in sheet 2 list the date paid, or pending. I would like the columne in sheet 1 to reflect this information.

**Richard Schollar** · 08-22-2008, 10:15 AM

You need some unique identifying reference common between the two sheets (eg an order number) - do you have this in the Paid sheet? If so, which column, and which column is the date you want to return in?

**ems.payroll** · 08-22-2008, 10:21 AM

The order number is in column J on sheet 1 and column B on sheet 2. The paid status is in column J on sheet 2 and I want it to matchup and show in column R on sheet 1.

**Richard Schollar** · 08-22-2008, 10:23 AM

Ok so in column R on Sheet1:

=IF(ISNA(MATCH(Sheet1!J2,Sheet2!B:B,0)),"Not Paid",INDEX(Sheet2!J:J,MATCH(Sheet1!J2,Sheet2!B:B,0)))

copied down.

**ems.payroll** · 08-22-2008, 11:12 AM

It doesn't work. It came back as Not Paid, but it was paid. If it is paid, it looks like the"PD WE 06-08-08". Also I forgot that sheet 2 can have multiple entries for each order. An example would be, for payroll 8-8 & 8-15, the order is listed as pending, then for payroll 8-22, it is listed as"PD WE 08-22-08". I only need the most recent entry listed on sheet 1.

**Richard Schollar** · 08-22-2008, 12:31 PM

Hi

Can you attach a small sample of a workbook with these two sheets and some sample data (remove anything confidential). It will make providing a working solution much easier. Thanks.

**ems.payroll** · 08-22-2008, 01:15 PM

Payroll Sample.xlsx

I had to delete over 12,000 lines of data to be able to upload, but I've included the customers begining with "A" for a sample. I've also deleted all addresses and phone numbers, so the column positions have cahnged. In Daily Compliance Column "O", is where I want to display the most recent (if any) data from Payroll Detail Column "I".

**Richard Schollar** · 08-22-2008, 02:30 PM

OK Thanks for that. Looking at your data I don't immediately see how to match Daily Compliance data to the Payroll Detail data. I can see that customer names are consistent between the two, but what I was expecting (perhaps incorrectly) that there would be some unique reference common to both (eg customers might appear multiple times, but each occurrence would have a unique ID attached in some field). Am I wrong to expect this, or maybe I have simply missed the unique identifier?

It can be done based on the customer name alone (and pull back the last reference matching that customer). To do this, ideally you should have the Payroll Detail sheet sorted by date (can be sorted by customer first, then by date). Would this be acceptable?

Richard

**ems.payroll** · 08-22-2008, 03:03 PM

On the Daily Compliance Tab, BAN # (column G), is the same as case ID (column B) on Payroll Detail. It's the result of two different departments.

**Richard Schollar** · 08-23-2008, 03:25 AM

OK, I have added a formula to column O of 'Daily Compliance':

=IF(ISNA(MATCH(G2,'Payroll Detail'!$B$2:$B$377,0)),"",LOOKUP(2,1/('Payroll Detail'!$B$2:$B$377='Daily Compliance'!G2),'Payroll Detail'!$I$2:$I$377))

I have also sorted 'Payroll Detail' sheet based on 'Bill Name' column then 'WE Update' column (both ascending - it is important that the latest date for each customer is at the bottom).

Many of the formula do not return a match so they return a blank ("") - presumably this is because the sheet does not contain the full data.

Please see attached.

Richard

**ems.payroll** · 08-25-2008, 08:53 AM

Thanks it worked wonderfully!

**ems.payroll** · 08-26-2008, 01:10 PM

Ok, the new challenge is this. If column "O" on Daily Compliance produces a blank result, then the ACT tabs need to be checked to see if there is a match. "ACT Legacy" does not have a column with the BAN #, but the order can be matched up using the confirmation # (column F on Daily Compliance and column E on ACT Legacy). ACT No Sale can be matched using either the BAN number (column G in Daily Compliance and column H in ACT No Sale) or the confirmation number (column F in both Daily Compliance and ACT No Sale). If there is a match, the result to be displayed from ACT Legacy is column G and from ACT No Sale is column E. As with the Payroll detail tab, an oreder may be listed more than once, and I would only need the most recent info. Payroll Sample.xlsx

**ems.payroll** · 08-27-2008, 01:51 PM

I've got four sheets, one lists the orders, one lists the paid or payable orders, and two list unpayable orders.

I tried the following formula to check and see if the order was paid, and if not check it against two other sheets to see if it is unpayable.

=IF(ISNA(MATCH(J2071,'Payroll Detail'!$B$2:$B$8200,0)),"",LOOKUP(2,1/('Payroll Detail'!$B$2:$B$8200=J2071),'Payroll Detail'!$J$2:$J$8200)),IF(ISNA(MATCH(F2071,'ACT Legacy'!$K$2:$K$2000,0)),"",LOOKUP(2,1/('ACT Legacy'!$K$2:$K$2000=F2071),'ACT Legacy'!$K$2:$K$2000)),IF(ISNA(MATCH(F2071,'ACT No Sale'!$K$2:$K$2000,0)),"",LOOKUP(2,1/('ACT No Sale'!$K$2:$K$2000=F2071),'ACT No Sale'!$K$2:$K$2000))

But is returns #VALUE!

Any suggestions?

**ems.payroll** · 08-28-2008, 02:30 PM

Bump No Response

**ems.payroll** · 08-29-2008, 10:25 AM

Ok, I'm getting closer, I no longer get the #VALUE! error, but it returns a blank value, instead of pulling the data from any of the sheets. I know I'm close, but what am I missing?

=IF(IF(ISNA(MATCH(F1518,'Payroll Detail'!$B$2:$B$8200,0)),"",LOOKUP(2,1/('Payroll Detail'!$B$2:$B$8200=F1518),'Payroll Detail'!$J$2:$J$8200))=0,IF(ISNA(MATCH(F1518,'ACT Legacy'!$K$2:$K$2000,0)),"",LOOKUP(2,1/('ACT Legacy'!$K$2:$K$2000=F1518),'ACT Legacy'!$Q$2:$Q$2000)),IF(ISNA(MATCH(F1518,'Payroll Detail'!$B$2:$B$8200,0)),IF(ISNA(MATCH(F1518,'ACT No Sale'!$K$2:$K$200,0)),"",LOOKUP(2,1/('ACT No Sale'!$K$2:$K$200=F1518),'ACT No Sale'!$G$2:$G$200)),LOOKUP(2,1/('Payroll Detail'!$B$2:$B$8200=F1518),'Payroll Detail'!$J$2:$J$8200)))

**ems.payroll** · 08-29-2008, 10:39 AM

This gets a result from Payroll Detail, but I'm still not getting anything from ACT Legacy or ACT No Sale:

=IF(IF(ISNA(MATCH(I1530,'Payroll Detail'!$B$2:$B$8200,0)),"",LOOKUP(2,1/('Payroll Detail'!$B$2:$B$8200=I1530),'Payroll Detail'!$J$2:$J$8200))=0,IF(IF(ISNA(MATCH(E1530,'ACT Legacy'!$K$2:$K$2000,0)),"",LOOKUP(2,1/('ACT Legacy'!$K$2:$K$2000=E1530),'ACT Legacy'!$Q$2:$Q$2000))=0,IF(ISNA(MATCH(E1530,'ACT No Sale'!$K$2:$K$200,0)),"",LOOKUP(2,1/('ACT No Sale'!$K$2:$K$200=E1530),'ACT No Sale'!$G$2:$G$200)),IF(ISNA(MATCH(E1530,'ACT Legacy'!$K$2:$K$2000,0)),"",LOOKUP(2,1/('ACT Legacy'!$K$2:$K$2000=E1530),'ACT Legacy'!$Q$2:$Q$2000))),IF(ISNA(MATCH(I1530,'Payroll Detail'!$B$2:$B$8200,0)),"",LOOKUP(2,1/('Payroll Detail'!$B$2:$B$8200=I1530),'Payroll Detail'!$J$2:$J$8200)))

**ems.payroll** · 10-03-2008, 04:50 PM

Please mark this as solved.

This is what I came up with. I ended up making a separate column for each report I wanted to check it against. Here's an example of the formula that finally worked. It's longer because I have it triple checking to bypass any potential misentered information.

=IF(IF(ISNA(MATCH('Daily Compliance'!J3595,'Payroll Detail'!$B$2:$B$15000,0)),"",LOOKUP(2,1/('Payroll Detail'!$B$2:$B$15000='Daily Compliance'!J3595),'Payroll Detail'!$J$2:$J$15000))="",IF(IF(ISNA(MATCH('Daily Compliance'!F3595,'Payroll Detail'!$R$2:$R$15000,0)),"",LOOKUP(18,1/('Payroll Detail'!$R$2:$R$15000='Daily Compliance'!F3595),'Payroll Detail'!$J$2:$J$15000))="",IF(ISNA(MATCH('Daily Compliance'!E3595,'Payroll Detail'!$V$2:$V$15000,0)),"",LOOKUP(22,1/('Payroll Detail'!$V$2:$V$15000='Daily Compliance'!E3595),'Payroll Detail'!$J$2:$J$15000)),IF(ISNA(MATCH('Daily Compliance'!F3595,'Payroll Detail'!$R$2:$R$15000,0)),"",LOOKUP(18,1/('Payroll Detail'!$R$2:$R$15000='Daily Compliance'!F3595),'Payroll Detail'!$J$2:$J$15000))),IF(ISNA(MATCH('Daily Compliance'!J3595,'Payroll Detail'!$B$2:$B$15000,0)),"",LOOKUP(2,1/('Payroll Detail'!$B$2:$B$15000='Daily Compliance'!J3595),'Payroll Detail'!$J$2:$J$15000)))