How to pull data from every third column using QUERY function

user_4315783 0
Viewed 47

I am using this formula but the same formula needs to be applied to every third column. ie: starting from D3:D, G3:G, J3:J, and so on... what is the best way to apply or pull the data from every third column. (data is on the second sheet called Sitemap)

Please advise and help, many many thanks much appreciated!

    =query({
'Sitemaps'!D3:D1000},
"Select * where Col1 is not null ")

Adding the sheet link maybe that will be more helpful to understand the situation, "AllURLs" needs to pull all links from Sitemaps into one list https://docs.google.com/spreadsheets/d/1AWGfA7cHmF3Q2kiX1xkQcoec6H5EPiHUXaiWENMzZkA/edit?usp=sharing

google-sheets
2 Answers

use:

=QUERY({INDIRECT("Sitemaps!"&
 ADDRESS(3,    (COLUMN($D1)-1)*COLUMN(A1)+1)&":"&
 ADDRESS(1000, (COLUMN($D1)-1)*COLUMN(A1)+1))},
 "where Col1 is not null")

and drag to the right

update:

use in B3:

=INDEX(IFERROR(REGEXEXTRACT(C3:C,"^(?:https?:\/\/)?(?:www\.)?([^\/]+)")))

use in C3:

=QUERY(FLATTEN(FILTER(IFERROR(Sitemaps!D3:1000), MOD(COLUMN(Sitemaps!D1:1)-1, 3)=0)), 
 "where Col1 is not null")
user_56326290

Try this:

=FILTER(FILTER(Sitemaps!D3:J,MOD(COLUMN(Sitemaps!D3:J)-4,3)=0),Sitemaps!D3:D<>"")

Just replace :J with whichever column is further to the right in your data set.

This one formula should produce all results, assuming that any rows that have data in Column D also have data in that row of every other included column, and that rows that are null in Column D are also null in that row of every other included column.

MOD is the modulus function. It returns whatever is left after dividing a number by another number. For instance, MOD(7,3) would return 1, because 7 divided by 3 is 6 with 1 left over. The leftover portion is the modulus.

We can apply this to your column numbers, since the ones you want to retrieve are evenly spaced three apart. We just need to start at a baseline of zero. Since Column D has a column number of 4, we can "zero out" that baseline by subtracting 4 from every column number. Only those columns that then are evenly divisible by 3 (i.e., those that, after subtracting 4, have a modulus of 0) are returned.

user_139032830
Related