Excel Question

[CycloWizard]

I need a spreadsheet function that will locate a zero value in column A and report that row's value in column B. Is there an easy way to do this? Column A may be negative, so sorting/minimizing won't really help.

 

[ThePoohContinuum]

Couldn't you just use the IF function? Such as, =IF(A1=0, A1, "") ?

 

[CycloWizard]

Yeah, that's what I thought of at first too, but it's pretty inefficient. I have to perform the test for every row (and I have thousands), then pull the one non-zero value. It works, but it's not exactly ideal. Thanks though. :beer:

 

[theknight571]

I've got a Macro that will do it for the selected column...

Make sure to select just the column... I don't have any "protection" built in. lol

To create the macro... from your worksheet:
- select Tools -> Macro -> Visual Basic Editor
- select Insert -> Module
- paste code into Module and save worksheet

To use the macro....
- select the range to fill with zeros
- select Tools -> Macro -> Macros
- select CheckZero
- click Run


Woooo.... my 1000th post... was a helpful one... I hope.

 

[Kyteland]

=MATCH(0,A:A,0)

That will find the row of the first 0 entry in column A. If there is possibly more than 1 row that is 0, you are out of luck doing it with a built in function. You are stuck checking each cell to see if it is 0.

 

[CycloWizard]

Thanks guys. Never knew about the 'match' function, but it looks like it works. :beer::beer:

 

[edcarman]

Hi

Are you looking for just the row number, or do you want to return the value in B that corresponds to 0 in A?

If so, the VLOOKUP function is the one to use. It searches the first row of a table and returns the corresponding value from any specified column (HLOOKUP does the same but searches the first row and returns from a specified row). This is great for large tables of, for example, thermodynamic property data.

Cheers
Ed