automatically skipping zeros when indexing and transposing

January 10, 2019 at 13:23:56
Specs: Windows 10
I AM USING THIS FORMULA:

=INDEX($B$2:$BG$10000,1+INT((ROW(B3)-1)/COLUMNS($B$2:$BG$10000)),MOD(ROW(B2)-1+COLUMNS($B$2:$BG$10000),COLUMNS($B$1:$BG$10000))+1)

BUT I WANT TO SKIP BLANK CELLS IN THE ROWS, IS THERE A QUICK ADD TO THIS FORMULA?


See More: automatically skipping zeros when indexing and transposing

Reply ↓  Report •

#1
January 10, 2019 at 17:51:31
Please don't post in All Caps.

All Caps in the internet equivalent of yelling and nobody likes to be yelled at.

We can hear you just fine when the proper case is used.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
January 11, 2019 at 06:21:10
I work in all CAPS, that's why it is in CAPS.

Reply ↓  Report •

#3
January 12, 2019 at 07:19:41
Here is a two step process that does not use any formulas
and should remove blanks and transpose your cells.

First select your range of cells, IE: B1:BG10000
Press the F5 Key
In the Go To popup window, select Special
In the Special popup window, select Blanks
Click OK
All your blanks cells should be highlighted.
Next, press CTRL key and Minus sign together
In the Delete popup window, select Shift Cells Up if it is not already selected.
Click OK

All your blank cells should now be gone.

Now, select your new range of cells, IE B1:BG10000
Press CTRL-C
Place your cursor in a new blank area that is large enough to accept your range
Press ALT-H-V-T to transpose your data.

See how that works for you.

MIKE

http://www.skeptic.com/


Reply ↓  Report •
Related Solutions


Ask Question