CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Raman Singh
Raman Singh

Reputation: 79

Fetch details from multiple spreadsheets

I have 2 spreadsheets where below data is given.Sheet1

Step 1: Where a user and its "ID" are provided in columns 'A' and 'B' (could be multiple).

Step 2: The outputs (in column 'C') provided against the "ID" should be displayed in column 'D' (If the output given in column 'C' contains "HI" and, or "Hello" then it should display in column 'D')

In Spreadsheet 2: Sheet 2

Step 3: In spreadsheet 2, column 'A'("ID") should match with column 'B' of spreadsheet 1 and display the number of: "HI" (In column 'C') "Hello" (In column 'D') "HI & Hello (Both)" (In column 'E') Anything other than "HI and/or Hello" (In column 'F')

Step 4: The 'Relationship Advisor' mentioned in column 'G' of spreadsheet 2 should reflect in column 'E' of spreadsheet 1.

Upvotes: 0

Views: 97

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9907

Most of what you're requesting is similar to your previous post and you should familiarize yourself with the search function. The Search function will return a true value if the text appears anywhere in the string, and false when it does not. This can enable you to accomplish most of the rest of your request.

To illustrate with your first sheet and populating column D you could use this dynamic formula (put in cell D2)

=filter(if(ISNUMBER(search("hi",C2:C)),"hi",
        if(ISNUMBER(search("hello",C2:C)),"hello",""))
             ,C2:C<>"")

What this does

  • The filter function ensures that we're making a formula for every cell from c2:c that has a value in it.
  • The first line is an if statement searches if "hi" appears in column c. If a value appears, it returns true and returns hi as the true condition of the if statement.
  • If the above if statement returns false, it repeats the search only for hello and in turn returns hello if found.
  • If false, it returns "".
  • The last line is connected to filtering.

If you wanted to return the exact case from column c you could use this:

=filter(if(ISNUMBER(search("hi",C2:C)), 
     mid(C2:C,search("hi",C2:C),2),
     if(ISNUMBER(search("hello",C2:C)),
        mid(C2:C,search("hello",C2:C),5),))
         ,C2:C<>"")

While this obviously doesn't address all of your to-do list, this concept should be able to get you close to resolving most of what you're doing.

Upvotes: 1

Related Questions