Hia,
Thank you in advance for your help.
I have data from G3 through to BBI98 and there are many blanks within this range.
What I am trying to do is concat 2 numbers, starting at E3, while skipping cells with the value of CountBlank = 1 and looping to the next column. This concat formula would continue down through column E.
The formula is simply: =IF(COUNTBLANK(G3)=1,"",(CONCAT($G$1,G3)))
Once this reaches G98, I need it to loop to H3 and start all over again.
=IF(COUNTBLANK(H3)=1,"",(CONCAT($H$1,H3)))
Once this reaches H98, I need it to loop to I3 and start all over again.
=IF(COUNTBLANK(I3)=1,"",(CONCAT($I$1,I3)))
Etc all the way to BBI98.
So, what I am hoping to do is create a macro that will loop this and skip any cells that CountBlank = 1 (since they all have formulas within them as well), while still looping through row 98. This is because there could be data in all rows but 20,21,22,etc but I dont want this formula to just stop at the blanks and skip the rest of the potential data through row 98.
Here is a very small example:
<tbody>
</tbody>
Is this possible!? Also, is it possible to have the formula not calculate the CountBlank = 1 value cells so there are not blank cells within the concat Column E?
Thank you!
Thank you in advance for your help.
I have data from G3 through to BBI98 and there are many blanks within this range.
What I am trying to do is concat 2 numbers, starting at E3, while skipping cells with the value of CountBlank = 1 and looping to the next column. This concat formula would continue down through column E.
The formula is simply: =IF(COUNTBLANK(G3)=1,"",(CONCAT($G$1,G3)))
Once this reaches G98, I need it to loop to H3 and start all over again.
=IF(COUNTBLANK(H3)=1,"",(CONCAT($H$1,H3)))
Once this reaches H98, I need it to loop to I3 and start all over again.
=IF(COUNTBLANK(I3)=1,"",(CONCAT($I$1,I3)))
Etc all the way to BBI98.
So, what I am hoping to do is create a macro that will loop this and skip any cells that CountBlank = 1 (since they all have formulas within them as well), while still looping through row 98. This is because there could be data in all rows but 20,21,22,etc but I dont want this formula to just stop at the blanks and skip the rest of the potential data through row 98.
Here is a very small example:
109 | 102 | 570 | 571 | 234 | |
I | I | I | I | S | |
BUS | 1558 | 1558 | 1558 | 1558 | |
BUS | 1255 | 1255 | 1255 | 1255 | |
CAN | 1730 | 1730 | 1730 | 1730 | |
CAR | 1205 | 1205 | 1205 | 1205 | |
HAM | 1689 | 1689 | 1689 | 1689 | |
HAR | 1083 | 1083 | 1083 | 1083 | |
HAR | 1306 | 1306 | 1306 | 1306 | |
IRB | 1275 | 1275 | 1275 | 1275 | |
KHA | 1174 | 1174 | 1174 | 1174 | |
MAI | 1306 | 1306 | 1306 | 1306 | |
MAY | 1720 | 1720 | 1720 | 1720 | |
MER | 1760 | 1760 | 1760 | 1760 | |
MOH | 1336 | 1336 | 1336 | 1336 | |
MUR | 1265 | 1265 | 1265 | 1265 | |
NOE | 1972 | 1972 | 1972 | 1972 | |
PAN | 1669 | 1669 | 1669 | 1669 | |
PAT | 1164 | 1164 | 1164 | 1164 | |
POW | 1811 | 1811 | 1811 | 1811 | |
RIV | 1619 | 1619 | 1619 | 1619 | |
TOR | 1568 | 1568 | 1568 | 1568 | |
LAC | |||||
AND | |||||
COO | |||||
DEM | |||||
GOD | |||||
MAR | |||||
MAY | |||||
OFF | |||||
SUC | |||||
WIL | |||||
COZ | |||||
KLA | |||||
PRI | |||||
SIL | |||||
THI | |||||
VAS | |||||
SEN | |||||
SHA | |||||
KAR | |||||
VEG | |||||
WHI | |||||
BAR | |||||
REZ | |||||
ROG | |||||
WAR | |||||
DUN | |||||
LAM | |||||
HEN | |||||
GAR | |||||
HAL | |||||
LEE | |||||
JON | |||||
KAP | |||||
KAS | |||||
KEN | |||||
LEE | |||||
LOC | |||||
LON | |||||
LOW | |||||
BRA | |||||
MIZ | |||||
RAN | |||||
ALT | |||||
BUR | |||||
GON | 1295 | ||||
ROB | |||||
PEL | |||||
BOL | 1295 | ||||
RIS | 1851 | ||||
SCH | 1609 | ||||
THO | 1083 | ||||
WAL | 1083 | ||||
WIL | 1669 | ||||
WEC | |||||
BAR | |||||
RIC | |||||
ART | |||||
BHA | |||||
BLI | |||||
DOE | |||||
GOR | |||||
KRI | |||||
LAN | |||||
LAR | |||||
MER | |||||
MIL | |||||
OUH | |||||
PAR | |||||
TAL | |||||
BER | |||||
ELL | |||||
AMA | |||||
CHI | |||||
PAT | |||||
SHA | |||||
PUG |
<tbody>
</tbody>
Is this possible!? Also, is it possible to have the formula not calculate the CountBlank = 1 value cells so there are not blank cells within the concat Column E?
Thank you!
Last edited: