SpreadEveryone: The Fetchland Excel wire
98 watchers
Aug 2022
10:50am, 12 Aug 2022
6,387 posts
|
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) |
Aug 2022
12:28pm, 12 Aug 2022
2,842 posts
|
Fitz
Thanks um, I'll have a play with that and see how it looks.
|
Aug 2022
1:23pm, 13 Aug 2022
5,140 posts
|
run free
Suggest using the results of the MATCh function in the ADDRESS function to provide the cell address
|
Aug 2022
8:44pm, 13 Aug 2022
6,448 posts
|
Pothunter
World Excel Championships to be broadcast live on ESPN: ladbible.com |
|
Aug 2022
3:16pm, 15 Aug 2022
2,847 posts
|
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. |
Sep 2022
3:40pm, 20 Sep 2022
16,878 posts
|
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! |
Sep 2022
3:41pm, 20 Sep 2022
139,337 posts
|
GregP
This sounds like a PERL job?
|
|
Sep 2022
3:42pm, 20 Sep 2022
139,338 posts
|
GregP
Does g1ngerrevolution still check in one wonders?
|
|
Sep 2022
3:47pm, 20 Sep 2022
16,879 posts
|
Garfield
Hmmm, I've not done terrible things with Perl recently...(about 10 years ago!)
|
|
Sep 2022
3:47pm, 20 Sep 2022
16,880 posts
|
Garfield
Python dataframes aren't very forgiving for me with this task.
|
Related Threads
-
Excel skills test
Mar 2019
-
Accounting Question
Oct 2017
-
Important excel files deleted - Please Help
Oct 2016
-
download training log to excel
Jan 2024
-
Excel boffins - your needed!
Sep 2018
-
World Cup sweepstake in excel
May 2018
-
HELP! Excel file corrupted and I need it!
Aug 2016
-
Running Club Spreadsheet to track PBs
Apr 2014
-
The Retirement Thread
Apr 2024
-
What do/would you miss about your job?
Feb 2024
