Values in two columns to one column

[Thomas Kuriakose]

Respected Sirs,

We have a price list in four columns for the same product from multiple sources. Kindly guide on how to get the price per material in one column. If one source is blank, it should go to the next source, if not a text then give the result.

The price needs to be compared between the sources and in one column list the lowest of the two or three and the second column the highest of the three. There are text entries also in the sources.

Kindly find attached the sheet for your kind reference.

Thank you very much,

with regards,
thomas

 

[NARAYANK991]

Hi ,

What is to be done if more than one column in V1 through V4 has numeric values ? Which value is to be put in column H ?

Narayan

 

[shrivallabha]

For column H you can try following approach (Control Shift Enter)
=INDEX(B2:E2,MATCH(1,1/ISNUMBER(B2:E2),0))

I will personally try not to have all results in one cell as they will lose practical usage as that will make the result as text but you can CONCATENATE all three formulas together to get results in one cell.

 

[Thomas Kuriakose]

Respected Sirs,

Thank you very much for your support on this query.

Respected Narayan Sir, for the one column in V1 through V4 has numeric values - H column should have the highest value.

Respected Shrivallabha Sir, Thank you very much for this solution.
Only one question, in this formula does it take the lowest price or the highest price.

Thank you Sirs, for your support,

Much appreciated,

with regards,
thomas

 

[NARAYANK991]

Hi ,

Then what is the difference between what is already present in column G and what you want in column H ? Will the two not be the same ?

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Correct, i missed to read this point. Apologies.

How to get the highest value using Sir, Shrivallabhas' formula which considers numbers.

=INDEX(B2:E2,MATCH(1,1/ISNUMBER(B2:E2),0))

Thank you very much,

with regards,
thomas

 

[shrivallabha]

I am not sure I follow your requirements accurately. I thought you wanted following:
- Start checking from V1 to V4
- Return first instance where you get numeric value.
That is what above formula is doing. Let us know your requirement if it is different.

 

[Thomas Kuriakose]

Respected Sir,

Thank your very much for all the support.

Based on the all the feedbacks and solution received, your solution is giving the required result, the first instance of numeric value.

How can we edit this formula get the highest value after ignoring blanks and text.

Thank you very much,

with regards,
thomas

 

[shrivallabha]

Respected Sir,

Thank your very much for all the support.

Based on the all the feedbacks and solution received, your solution is giving the required result, the first instance of numeric value.

How can we edit this formula get the highest value after ignoring blanks and text.

Thank you very much,

with regards,
thomas

Your formula in column G is working correctly to give MAX. Are there any concerns?

 

[Thomas Kuriakose]

Respected Sirs,

Thank you very much for this information.

The actual data consist of 90k rows and want to be sure with the correct prices per product are picked up and which will ignore blanks and text.

I will use MAX and check the data. With the base data of price, we have to compute other values in separate columns.

Thank you very much once again,

with regards,
thomas