Combine 2 Columns from 2 different sheets with 2 conditions

[Sekars]

Hello Friends

I need to combine 2 columns from Sheet1 and Sheet2, in both sheets Column C should be combined.

But if any texts repeated then it should be plotted as is i.e., not removing duplicates.
But if any numbers repeated then it should be plotted once by removing the duplicate entires.

i.e., XXX need to keep as without removing duplicates, but number cell duplicates need to be removed.

Now I had hardcored the expected results in the Sheet3, Column C light yellow cells.

thanks in advance

[JohnTopley]

You have an answer (repeated thread)....?????

http://www.excelforum.com/showthread...t=#post4565105

[Glenn Kennedy]

This time it's with two conditions. However, I didn't understand the request the first time round!!!

[samba_ravi]

Hi sekar is any possible to appear the same number in both the sheets (for duplicates)

[JohnTopley]

@glenn,
Join 2 columns (one after the other), removing duplicate numbers BUT leaving duplicate text ("XXX")

[Sekars]

Yes, Please remove the duplicate numbers (Duplicate Numbers should be displayed as one), and keep the duplicate texts "XXX" as many time which is occured.

[samba_ravi]

E4

Please Login or Register  to view this content.

Try this array formula and copy towards down

[Sekars]

Hello Shiva

Thanks for your formula, I have pasted this array formula to E4 and dragged upto E53, it is working from E17 to E53 i.e., the formula shows blank in E4:E16.


Thanks again for your formula.

[samba_ravi]

Hello Shiva

Thanks for your formula, I have pasted this array formula to E4 and dragged upto E53, it is working from E17 to E53 i.e., the formula shows blank in E4:E16.


Thanks again for your formula.

see the attachment

[Sekars]

Hello Siva

Thanks for this file, But my data is frequently changing and i will add / remove the rows in Column C (and Sl. No. in Column A) in both Sheets. Can we is it possible to change the $C$19, $C$24 and $C$25 to $C$100 like this which including current blank cells may be populated in future.

Thanks again

[samba_ravi]

Then try this array formula
=IFERROR(INDEX(Sheet1!C:C,SMALL(IFERROR(IF(IF(Sheet1!$C$4:$C$100="XXX",ROW(Sheet1!$C$4:$C$100),MATCH(Sheet1!$C$4:$C$100,Sheet1!$C:$C,0))-ROW(Sheet1!$C$4:$C$100)=0,ROW(Sheet1!$C$4:$C$100)),""),ROWS(E$4:E4))),IFERROR(INDEX(Sheet2!C:C,SMALL(IF(IF(Sheet2!$C$4:$C$100="XXX",ROW(Sheet2!$C$4:$C$100),IFERROR(MATCH(Sheet2!$C$4:$C$100,Sheet2!$C:$C,0),0))-ROW(Sheet2!$C$4:$C$100)=0,ROW(Sheet2!$C$4:$C$100)),ROWS(E$4:E4)-SUMPRODUCT((FREQUENCY(Sheet1!$C$4:$C$100,Sheet1!$C$4:$C$100)>0)+0)-COUNTIF(Sheet1!$C$4:$C$100,"XXX"))),""))

[Sekars]

Hello Shiva

Thank you,

This is my perfect requirement

Thanks again.