# 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 " ".

#### Attachments

• 116.8 KB Views: 6

#### vletm

##### Excel Ninja
cyliyu
Something like this ... for Your need to si...fy something.

#### Attachments

• 70.7 KB Views: 7

#### cyliyu

##### Member
vletm,
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

• 77.1 KB Views: 6
• cyliyu

#### vletm

##### Excel Ninja
cyliyu
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

• 37.9 KB Views: 3
Last edited:
• cyliyu

#### cyliyu

##### Member
cyliyu
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.