Total Empty column between two characters

bdtran

New Member
Joined
Oct 11, 2018
Messages
33
Hello Team,

I need your help with a formula to calculate how many empty column between of the letter “A” as in the row#2. The result that I am looking for should be the same as in row#1 as shown below.

Any help would be greatly appreciated.
BDT


ABCDFGHIJKLMNOPQRSTUVWXYZAA
ROW#112172
ROW#2AAAAAAAAAAA

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
in A1
=IF(AND(A2="",B2="A"),COLUMN()-MAX(IF($A2:A2="A",COLUMN(($A2:A2)))),"")
Array formula, use Ctrl-Shift-Enter

copy along as far as AA1
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Hi,

Below is same fundamental logic as Special-K99 but without invoking Ctrl+Shift+Enter:

Code:
=IF(AND(C2="A",B2=""),COLUMN()-AGGREGATE(14,6,($A2:B2<>"")*COLUMN($A2:B2),1),"")
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Here's another way:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">2</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A</td><td style="text-align: right;;"></td><td style=";">A</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style="text-align: right;;"></td><td style=";">A</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet688</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">A2="",B2="A"</font>),COLUMN(<font color="Red"></font>)-IFERROR(<font color="Red">LOOKUP(<font color="Green">"A",$A2:A2,COLUMN(<font color="Purple">$A2:A2</font>)</font>),0</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

A1 formula copied across.
 
Last edited:

bdtran

New Member
Joined
Oct 11, 2018
Messages
33
Special-K99,

I tested your formula and it worked.

Thank you so much!
BDT
 

bdtran

New Member
Joined
Oct 11, 2018
Messages
33
Aryatect,

I used for your formula and got the result I am looking for.

Thanks much for your help.
BDT
 

bdtran

New Member
Joined
Oct 11, 2018
Messages
33
Hi,

Here's another way:

ABCDEFGHIJKLM
1121
2AAAAAAAAA

<tbody>
</tbody>
Sheet688

Worksheet Formulas
CellFormula
A1=IF(AND(A2="",B2="A"),COLUMN()-IFERROR(LOOKUP("A",$A2:A2,COLUMN($A2:A2)),0),"")

<tbody>
</tbody>

<tbody>
</tbody>



A1 formula copied across.

JTAKW,

I tested your formula and somehow I got the unexpected result as below. Not sure it was on my end error.

I copied to the A1 as instructed.

ABCDEFGHIJKLMNOPQRSTUVWXYZ
1157107
AAAAAAAAAAAAAA

<tbody>
</tbody>

I should get

S1 = 1
V1 = 2
Y1 = 2

Thanks,
BDT
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Not sure why, but just swap out the "A" with "zzz" like below:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">5</td><td style=";"></td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">2</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">2</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A</td><td style="text-align: right;;"></td><td style=";">A</td><td style=";">A</td><td style="text-align: right;;"></td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">A</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet688</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">A2="",B2="A"</font>),COLUMN(<font color="Red"></font>)-IFERROR(<font color="Red">LOOKUP(<font color="Green">"zzz",$A2:A2,COLUMN(<font color="Purple">$A2:A2</font>)</font>),0</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

A1 formula copied across.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,051
Office Version
2010
Platform
Windows
Here is another normally entered solution...

If it is possible for cell A2 to be blank, then put this formula in cell A1, otherwise leave cell A1 blank...

=IF(AND(A2="",B2="A"),1,"")

Now put his formula in cell B1 and copy it across to the end of your data...

=IF(AND(B2="",C2="A"),COUNTIF($A2:B2,"")-SUM($A1:A1),"")
 

bdtran

New Member
Joined
Oct 11, 2018
Messages
33
All,

While checking the data, I encountered a sheet containing the sample data as in the table below. Similarly, I need a formula for the result in row#1 with total of columns (not empty) between the letter "A".

Thank you so much for all the help.
BDT


ABCDEFGHIJKLMNOPQRSTUVWXYZ
12128
2A10AAA0AAAAA11A11010110A

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,102,736
Messages
5,488,566
Members
407,646
Latest member
utl1095

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top