0

I have certain data where I want the unique Retailer name across multiple sheets and multiple columns containing "Retailer Name" as the heading. I've shared the editable Google Sheet link, kindly help!

https://docs.google.com/spreadsheets/d/1RM21N_w7mWTltMQi5Iq-p8iBaqJwegj94NwZVxfy5ng/edit#gid=0

2 Answers 2

0

The following formula combines the data from the "Gadget Sack", "Bloomindales" and "Legacy" sheets.

=query(unique(flatten(query({'Gadget Shack'!A2:AC},"Select "&ArrayFormula(join(", ",text(query(transpose({column('Gadget Shack'!A2:AC2);mod(column('Gadget Shack'!A2:AC2)-column('Gadget Shack'!A2),3)}),"Select Col1 where Col2=0"),"Col0")))),query({Bloomingdales!A2:AC},"Select "&ArrayFormula(join(", ",text(query(transpose({column(Bloomingdales!A2:AC2);mod(column(Bloomingdales!A2:AC2)-column(Bloomingdales!A2),3)}),"Select Col1 where Col2=0"),"Col0")))),query({Legacy!A2:AC},"Select "&ArrayFormula(join(", ",text(query(transpose({column(Legacy!A2:AC2);mod(column(Legacy!A2:AC2)-column(Legacy!A2),3)}),"Select Col1 where Col2=0"),"Col0")))))),"select Col1 where Col1 is not null order by Col1")


The formula consists of three elements:

  • query({'Gadget Shack'!A2:AC},"Select "&ArrayFormula(join(", ",text(query(transpose({column('Gadget Shack'!A2:AC2);mod(column('Gadget Shack'!A2:AC2)-column('Gadget Shack'!A2),3)}),"Select Col1 where Col2=0"),"Col0"))))
  • flatten (query, query, query) output the result in a single column
  • unique - get the unique values for the Retailer names
  • query("select Col1 where Col1 is not null order by Col1") - remove the blank rows from the results and output the results in alpha order.
1
  • Thank you for the solution, it's very helpful!
    – Santosh J
    Jun 27 at 15:51
0

=unique(flatten(A2:A7,C2:C7,E2:E7))

  • Flatten consists of data from several columns and converts that data into a single column

Sample Data

data


Output

output

2
  • Is there an easier way to select "A:Z" where cell 1 contains "Retailer Name". Reason is there are too many columns which have the name "Retailer Name" and the data from sheets 2,3 and 4 will be merged in sheet 1. Maybe you'd get a clearer picture if I shared the whole sheet..
    – Santosh J
    Jun 27 at 9:43
  • Try the other answer.
    – Tedinoz
    Jun 27 at 12:59

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.