Google-sheets – Find transactions from new paypal addresses

google sheets

Demo sheet – https://docs.google.com/spreadsheets/d/1H78_Y7H_KOe1HKEKAdqcucUYV1NjsQBstUERzvURJcs/edit?usp=sharing

This is used in an attempt to reconcile paypal payments from different domains – quite often the paypal email address used doesnt match a domain.

Please help me search through a list of Paypal Receipts for from email that we haven't seen previously and recorded in paypal email in either of Domains 1 or Domains 2 sheets in the same spreadsheet. paypal email can contain multiple emails separated by a comma.

Record paypal email in New emails with Date of transaction in Date of transaction order. Notes is added later as we track down which domain sent the payment.

When I track down which domain the paypal email is attached to, I enter the paypal email into the Domains 1 or 2 sheet. I realise that when I do that, the line will probably disappear from the New emails sheet and i will have to move the Notes up, but thats a minor problem, and I don't think there is a better way to do it.

I have included my rather clunky efforts to achieve this on the 'My attempt' sheet – I think it has the right results, but there must be a more elegant way to achieve this?

Best Answer

You want to compare a list of new email addresses against addresses on two separate established lists. Your goal is to identify whether/which any of the new email addresses are NOT on the established lists.

Your established lists are on sheets "Domains 1" and "Domains 2". Each of these sheets have 999 records, however in some cases there are two email addresses separated by a comma. The actual number of email addresses on these two sheets is 2011. Note: email addresses on the established lists may duplicated several times.

The new email addresses are on sheet="Paypal Receipts". There are 3,375 addresses on 3,379 rows; 4 rows are blank.

Enter this formula in Cell L2 of on sheet="Paypal Receipts". Any email address in Column K that is not already included on the established lists on "Domain 1" and "Domain 2", will be flagged as "New".

={ARRAYFORMULA(ifna(vlookup(K2:K3380,
ARRAYFORMULA(
  TRIM(
    SPLIT(
      TRANSPOSE(
        SPLIT(
          QUERY(
            TRANSPOSE(
              QUERY(
                TRANSPOSE(
                  IF(
                    IFERROR(
                      SPLIT(query({'Domains 1'!A2:J1000;'Domains 2'!A2:J1000},"select Col10"), ",")
                    )="",
                   ,"♠"&"♦"&SPLIT(query({'Domains 1'!A2:J1000;'Domains 2'!A2:J1000},"select Col10"), ",")
                  )
                )
               ,,99^99)
             )
           ,,99^99)
        , "♠")
      )
, "♦")
  )
),1,0),"New"))}

Then filter sheet="Paypal Receipts" for all values in Column L = "New"

Props @Player0 Split Comma Separated Values in a String against ID with Google Sheets that I have clumsily shoehorned into this answer.

Related Topic