0

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?

  • Welcome. Would you please add a brief description of your search/research efforts as is suggested in How do I ask a good question?. Would you also describe your own efforts to solve your problem. – Tedinoz Jul 30 at 1:02
  • Thanks - I have details of my own efforts at the bottom - I was a little embarrassed TBH. – tzonov Jul 30 at 5:55
  • Excellent -One minor problem: these is no "My Attempt" sheet attached to the spreadsheet – Tedinoz Jul 30 at 23:09
  • Sorry - its there now! – tzonov Jul 31 at 0:30
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.