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

dhman08

New Member
Joined
Nov 16, 2016
Messages
18
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.



Column ABCDEFGHIJKL
VendorPRICEVendorPRICEVendorPRICEVendorPRICEVendorPRICETotal VendorTotal PRICE
BCEI1.1AAPL5.2SPY3.3
TCON2.2UTX1.09
VNR3.2MSFT4.5
SPYG1.05VTR1.56AMGN3.2

<tbody>
</tbody>
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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)
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
That'll only work for my sample data now, but if I have a massive spreadsheet containing 30000 row it won't be efficient.
 
Upvote 0
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?
 
Upvote 0
dhman08,

How about something like this?



Excel 2007
ABCDEFGHIJKL
1VendorPRICEVendorPRICEVendorPRICEVendorPRICEVendorPRICETotal VendorTotal PRICE
2BCEI1.1AAPL5.2SPY3.3BCEIAAPLSPY9.60
3TCON2.2UTX1.09TCONUTX3.29
4VNR3.2MSFT4.5VNRMSFT7.70
5SPYG1.05VTR1.56AMGN3.2SPYGVTRAMGN5.81
6
Sheet1
Cell Formulas
RangeFormula
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top