0

I have a list that contains people's names in one column and their company's names in a second column. At the top of each column there is a label, i.e "Name" for column A, "Company" for column B.

Data I have

The way a program I want to use works is to read data from a master list in one column, and it differentiates the type of data contained in the first column using second column, like this:

Data I need

The ultimate goal is to have this data pulled automatically from an outside source, which is stored on one tab. There will then be another tab containing this master list, one column containing people's names and various company names, and a second column with the first column's data type (i.e 'Person' or 'Company', as pictured above) right next to it.

So far I have been able to populate individual columns in the second tab by using Sheet's "QUERY" function,

`QUERY('Importer'!A2:A)`  

which allows the column to update very nicely every time the other tab is updated from the outside source, the problem is that because I don't know ahead of time how long the referenced column is going to be, I can't add a second 'QUERY' function to pull data from another column below it.

Any tips on how to solve this would be much appreciated. Thanks!

0

1 Answer 1

2

You could try:

enter image description here

Formula in C1:

=ARRAYFORMULA(SORT(SPLIT(FLATTEN(QUERY({IF(A2:A="",,A2:A&"|Name"),IF(B2:B="",,B2:B&"|Company")},"where Col1 is not null")),"|"),2,0,1,1))

A little bit of explaination (inside-out):

  • IF() - Create concatenated strings within a 2d-array using {1,2} construct.
  • QUERY() - Remove all empty rows from the array.
  • FLATTEN() - Flatten the resulting array into a single column
  • SPLIT() - Turn the concatenated values back into a 2d-array.
  • SORT() - Sort the returned array on both collumns to get the wanted result.
  • ARRAYFORMULA() - Everything needs to be wrapped as an array entered formula.

EDIT

For those who want to play around with the data for themselves:

Name Company
Ash Quintana Company A
Aubrey Wickens Company B
Cameron Toth Company C
Harley Holbrook Company D
Jac Argent Company E
Jamie Ziya Company F
Jess Patel Company G
Lane Bergman Company H
Madison Taub Company I
Troy Easton Company J
1
  • Yes! This did exactly what I needed it to do. The only thing I had to do was add '<sheetName>'! before both the references to the columns in the formula in order to pull in the data from another sheet. I'm assuming that's the best way to do that? Much appreciated!
    – fillway
    Jan 20, 2021 at 20:48

Not the answer you're looking for? Browse other questions tagged .