Picking Data from Multiple Columns

October 15, 2018 at 21:43:50
Specs: Windows 7
I have 4 columns in an excel sheet with three columns containing some values including some #N/A values, some blanks and some "Unknown" and some valid values in all 3 columns. I want to pick the valid values from all these columns into the fourth columns with column#1 as priority if not N/A, then Column#2, followed by the third Column and such that the N/A, empty and Unknown values are omitted. I have created a nested IF with ISNA and OR function but it is not working.

Any idea/help would be appreciated

message edited by imadnanfarooq


See More: Picking Data from Multiple Columns

Reply ↓  Report •

#1
October 16, 2018 at 03:59:54
What does the "valid data" look like? Is it text, numbers, something else?

Perhaps a short example of your data layout would help so that we know what we are dealing with.

Please click on the following link before posting your example data.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
October 16, 2018 at 09:01:32
I want to pick the valid values from all these columns into the fourth columns
that the N/A, empty and Unknown values are omitted

Try this:

With your Data in Columns A & B & C in cell D1 enter this formula:

=IF(AND(A1<>"N/A",A1<>"Unknown",A1<>""),A1,IF(AND(B1<>"N/A",B1<>"Unknown",B1<>""),B1,IF(AND(C1<>"N/A",C1<>"Unknown",C1<>""),C1,"")))

See if that is what you want.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#3
October 16, 2018 at 15:58:29
Hi,
Thanks for your replies. I have tried using the below formula but it does not work.

=IF(AND(NOT(ISNA(X2)),X2<>"Unknown"),X2,IF(AND.......

It looks like the above always returns true and it gets data from X2 only. #N/A are the values as a result of Formulas.
Any help would be appreciated

Here is how it looks like

Name X Y Z Useful-Data
Alpha #N/A HP Cisco HP
Beta #N/A #N/A Cisco Cisco
Gamma Cisco #N/A Unknown Cisco
Delta HP #N/A #N/A HP
Zeta Unknown #N/A Microsoft Microsoft


Reply ↓  Report •

Related Solutions

#4
October 17, 2018 at 17:20:48
Please click on the following How-To link and read the instructions on how to post example data in this forum. Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question