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]

 

[AliGW]

What would your expected outcomes be in column K for the sample data? Why does it need to be text and not a number?

Is there a reason why you cannot just use SUM? e.g. =SUM(A2:J2)

 

[dhman08]

Looking at the table that I have for Row 1, expected outcome would be BCEIAAPLSPY. Column K deals strictly with entity names. Hence the text, not a number.

I believe sum will return a 0 value, not the expected value I return

 

[AliGW]

How about this? Blank cells are ignored.

=A2&C2&E2&G2&I2

 

[dhman08]

That'll only work for my sample data now, but if I have a massive spreadsheet containing 30000 row it won't be efficient.

 

[AliGW]

Why not? It's just a case of copying the formula down. Or are you saying that your sample data is not properly representative of the real data?

 

[hiker95]

dhman08,

How about something like this?

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1	Vendor	PRICE	Vendor	PRICE	Vendor	PRICE	Vendor	PRICE	Vendor	PRICE	Total Vendor	Total PRICE
2	BCEI	1.1			AAPL	5.2			SPY	3.3	BCEIAAPLSPY	9.60
3	TCON	2.2							UTX	1.09	TCONUTX	3.29
4			VNR	3.2			MSFT	4.5			VNRMSFT	7.70
5	SPYG	1.05			VTR	1.56	AMGN	3.2			SPYGVTRAMGN	5.81
6
Sheet1

Cell Formulas
Range	Formula
K2	=A2&C2&E2&G2&I2
L2	=B2+D2+F2+H2+J2

The formula in cell K2, copied down:

=A2&C2&E2&G2&I2


The formula in cell L2, copied down:

=B2+D2+F2+H2+J2

 

[dhman08]

That is a perfect solution for now, but if I 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 that. Thank you for your suggestion though

 

[hiker95]

That is a perfect solution for now

dhman08,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[AliGW]

So, are you saying that you are looking for an automated solution? If so, you probably need to add VBA to your thread title to attract the right helpers. I'm afraid VBA is not my speciality.