• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combine 2 Column into 1

cyliyu

Member
I have 2 columns in sheet2 performing almost the same function
Column F =IF(D6="","",IF(ISNA(MATCH(D6&"_"&E6,Sheet3!$K$4:$K$500,0)),"","New"))
Column G =IF(D6="","",IF(ISNA(MATCH(D6&"_"&E6,Sheet3!$E$4:$E$500,0)),"","SST"))

Both are using a helper column in Sheet3, Column E and K.

I would like to simplify into
1) Without using a helper column
2) Combine column F and G into one column to display either New, SST or " ".

Please help and give some advice.
 

Attachments

cyliyu

Member
vletm,
Your solution works for my master spreadsheet. Thanks.
But the s/no (E6) and d/c (F6) must be in 2 separate columns as part of the requirement.
Is it possible to maintain E6 and F6 and remove the "helper" column which was in your solution that has both E6_F6 combined?
 

Peter Bartholomew

Well-Known Member
It is possible to perform the concatenation within the formula (ideally a named formula) but an alternative way of doing away with helper columns is to use formulas to take the product of the Boolean arrays, giving, using each of two defined names IsNew? and IsSST?
= ISNUMBER( MATCH( 1, (NEW[Unit S/No]=Output[@[Serial No]]) * (NEW[Unit Datecode]=Output[@[Date Code]]), 0 ) )
= ISNUMBER( MATCH( 1, (SST[Unit S/No]=Output[@[Serial No]]) * (SST[Unit Datecode]=Output[@[Date Code]]), 0 ) )

which are then used in the worksheet formula
= IF( IsNew?, "NEW", IF( IsSST?, "SST", "" ) )
 

Attachments

vletm

Excel Ninja
cyliyu
Your requirement was:
I would like to simplify into
1) Without using a helper column
2) Combine column F and G into one column to display either New, SST or " ".

I couldn't si...fy more then with basic Excel.
... and many things are possible,
You need to ask with really needed requirement.
... or You could test this sample.
 

Attachments

Last edited:

cyliyu

Member
cyliyu
Your requirement was:
I would like to simplify into
1) Without using a helper column
2) Combine column F and G into one column to display either New, SST or " ".

I couldn't si...fy more then with basic Excel.
... and many things are possible,
You need to ask with really needed requirement.
... or You could test this sample.
Thanks. VBA code help.
 

cyliyu

Member
It is possible to perform the concatenation within the formula (ideally a named formula) but an alternative way of doing away with helper columns is to use formulas to take the product of the Boolean arrays, giving, using each of two defined names IsNew? and IsSST?
= ISNUMBER( MATCH( 1, (NEW[Unit S/No]=Output[@[Serial No]]) * (NEW[Unit Datecode]=Output[@[Date Code]]), 0 ) )
= ISNUMBER( MATCH( 1, (SST[Unit S/No]=Output[@[Serial No]]) * (SST[Unit Datecode]=Output[@[Date Code]]), 0 ) )

which are then used in the worksheet formula
= IF( IsNew?, "NEW", IF( IsSST?, "SST", "" ) )
Thanks Peter.
Learnt a new method today. appreciate.
 
Top