SpreadEveryone: The Fetchland Excel wire

101 watchers

um Aug 2022 10:50am, 12 Aug 2022 6,387 posts	0 um OK, so =MATCH(TRUE,INDEX(A4:Z4<>"",),0) will return a numeric value of the first non empty column in the array, so in this case, 1 is A, 2 is B etc. You can then use the countblank substituting the numeric with the cell alpha. eg something like =COUNTBLANK(INDIRECT("F"&"4:z4")) - if the value from the first function was 6, where you have a table somewhere substituting column numbers to letters and swap the "F" for that. You could build it all into one formula in a cell, but I'd be tempted to do it in a separate sheet. So (eg) for each row, col A has the match function referenced back tt the source sheet. Col B has the empty cell count Or - you could just do countblank for the whole row, minus the result of the match formula to remove any preceding empty cells. May be easier. (also check the result to see if you need to add or subtract one to get the right results)
Fitz Aug 2022 12:28pm, 12 Aug 2022 2,842 posts	0 Fitz Thanks um, I'll have a play with that and see how it looks.
run free Aug 2022 1:23pm, 13 Aug 2022 5,140 posts	0 run free Suggest using the results of the MATCh function in the ADDRESS function to provide the cell address
Pothunter Aug 2022 8:44pm, 13 Aug 2022 6,448 posts	0 Pothunter World Excel Championships to be broadcast live on ESPN: ladbible.com
Fitz Aug 2022 3:16pm, 15 Aug 2022 2,847 posts	0 Fitz Just looping back to um, that formula for counting blanks to the right of the first non-blank worked a treat, in conjunction with your last comment about counting all the blanks and using the difference in my calcs. Job very much done, thank you.
Garfield Sep 2022 3:40pm, 20 Sep 2022 16,878 posts	0 Garfield I wonder if you'd be able to help...I've generated 170 single column csvs via Python and wondered if they all could be combined (as separate columns). They don't necessarily have the same number of rows, but just need to be stapled together. Each first item in the file is a country name, and subsequent rows contain the titles of guides for each country. Any suggestions would be welcome...and many thanks in advance!
GregP Sep 2022 3:41pm, 20 Sep 2022 139,337 posts	0 GregP This sounds like a PERL job?
GregP Sep 2022 3:42pm, 20 Sep 2022 139,338 posts	0 GregP Does g1ngerrevolution still check in one wonders?
Garfield Sep 2022 3:47pm, 20 Sep 2022 16,879 posts	0 Garfield Hmmm, I've not done terrible things with Perl recently...(about 10 years ago!)
Garfield Sep 2022 3:47pm, 20 Sep 2022 16,880 posts	0 Garfield Python dataframes aren't very forgiving for me with this task.