Checking Whether Cells in a Range are Blank, and returning values if it isn't

[dhman08]

New to the excel world and I'm baffled on how to fix this problem. Any help will be much appreciated. This is what I'm trying to do - I have a range of data which includes blank and non-blank cells. What I'm trying to do is a) For each row check to see if the cell is blank. This check will be done for every other cell starting with column A and b) if it is, I don't want anything returned, but if it isn't I'd like the value to be returned.

Ideally, what I'd like to do is perform all these checks in one cell and the value to the returned in the same cell at the end of the row. So for example, if I look at row 1, column A, E, I have values, but column C and G have no values. So I'd like the formula to essentially skip column C and E and add A, E, and I and return them as a text string in Column K.



[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD]PRICE[/TD]
[TD]Vendor[/TD]
[TD]PRICE[/TD]
[TD]Vendor[/TD]
[TD]PRICE[/TD]
[TD]Vendor[/TD]
[TD]PRICE[/TD]
[TD]Vendor[/TD]
[TD]PRICE[/TD]
[TD]Total Vendor[/TD]
[TD]Total PRICE[/TD]
[/TR]
[TR]
[TD]BCEI[/TD]
[TD]1.1[/TD]
[TD][/TD]
[TD][/TD]
[TD]AAPL[/TD]
[TD]5.2[/TD]
[TD][/TD]
[TD][/TD]
[TD]SPY[/TD]
[TD]3.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TCON[/TD]
[TD]2.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]UTX[/TD]
[TD]1.09[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]VNR[/TD]
[TD]3.2[/TD]
[TD][/TD]
[TD][/TD]
[TD]MSFT[/TD]
[TD]4.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SPYG[/TD]
[TD]1.05[/TD]
[TD][/TD]
[TD][/TD]
[TD]VTR[/TD]
[TD]1.56[/TD]
[TD]AMGN[/TD]
[TD]3.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dhman08]

dhman08,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

Are you aware of any better solution than this? Even though this will work for now, it won't work 2 weeks from now when I will have 30000 rows and 5000 columns that will not be a efficient way to do this. So I'm trying to see if there are better solution than this

 

[dhman08]

I don't think VBA is the only solution. I think there's some sort of excel formula that can help me achieve my goal. I'm just not ware of that.

 

[AliGW]

See post #10 - your thread title is not really adequate for what you seem to want.

 

[AliGW]

I don't think VBA is the only solution. I think there's some sort of excel formula that can help me achieve my goal. I'm just not ware of that.

Any Excel formula will need copying down. In terms of the rest, we would need to know more about the real layout of your data. Is it always every other column that needs concatenating? You seem to know what you don't want, but aren't really making it clear what you do want, so it's quite hard to advise!

 

[hiker95]

Even though this will work for now, it won't work 2 weeks from now when I will have 30000 rows and 5000 columns that will not be a efficient way to do this.

dhman08,

A macro solution could work, but, if there is text in all of the Vendor columns, then, Excel will not be able to display all the text information in one cell.

 

[dhman08]

Any Excel formula will need copying down. In terms of the rest, we would need to know more about the real layout of your data. Is it always every other column that needs concatenating? You seem to know what you don't want, but aren't really making it clear what you do want, so it's quite hard to advise!

I'm aware that excel formula will need to be copied and I have no problem with that. I think there's a way to accomplish what I'm trying to do without vba. I'm just trying to find that formula. And as I've mentioned in my original post: my goal is to check whether cells in a row are empty. If a cell is empty/blank ignore that cell. If it isn't pick the text that's in that cell, do the same check for every other cell from the next cell over, and concatenate all the cells that have values.