Gaps

[AlreadyLost]

If I want to identify the gaps between shee1 and sheet2 into sheet3? Is there a simpler way of formula to do this on Sheet3?

[sheet 1 sheet 2 Result Gap sheet3
a b a
b c y
c z z
y 0 0]

Thanks again.

 

[SkipVought]

Hi,

Please understand that we can only SEE what you write and from your written definition of your problem, I'm AlreadyLost, too!

What GAPS are you referring to?

your Example does not line up. Please check out the Process TGML tag notation link below in order to post a CLEAR, CONCISE and COMPLETE question with examples.

Skip,
[sub]
[red][/red]
[/sub]

 

[AlreadyLost]

Let me try this again. I need to compare sheet1 and sheet 2. Then get only the difference between the two sheet and put it on ResultGapSheet3.

sheet 1 sheet 2 ResultGapsheet3
a b a
b c y
c z z
y 0 0

 

[AlreadyLost]

Sheet 1 has "a", "b", "c","y'
Sheet 2 has "b", "c","z","0"
ResultGapSheet3 should show "a","z", "y","0"

 

[SkipVought]

Please post an example of each sheet BEFORE the compare.

Then post an example of the compare result base on the values in each sheet.

Skip,
[sub]
[red][/red]
[/sub]

 

[AlreadyLost]

Sheet 1 and sheet 2 is before.. ResultGapSheet3 is after.

 

[SkipVought]

Can you understand that I CANNOT SEE what is ON SHEET 1 and what is ON SHEET 2???

How is this supposed to help me understand???

"Sheet 1 and sheet 2 is before.. ResultGapSheet3 is after."

I could have told you THAT!!!

Skip,
[sub]
[red][/red]
[/sub]

 

[fneily]

I'm not an Excel VBA expert, but here's one way, not the way you want, but it does return unigue values. Place the values of sheet1 and sheet2 in one column, so the values of sheet2 are under sheet1's. No blank cells or this will not work. Select all the data and give it a name. For mine I named the range Data. Then Select the same amount of cells in a column next to it. Type in the following (check your typing!). After you finish, do CTRL+SHIFT+ENTER to enter it. You'll see brackets around the formula. This is called an Array formula. You'll also see your answer. You'll notice some #Num's. This is just where the formula doesn't apply. Note: You CANNOT type the brackets - you must do CTRL+SHIFT+ENTER.

=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data))),MATCH(Data,Data,0),""),ROW(INDIRECT("1:"&ROWS(Data)))))

 

[KenWright]

If you don't mind a couple of interim steps,

on a copy of your sheet 1, add a column at the start and put Sht1 against every record.

Now copy all the data from Sht2 underneath it and put Sht2 against each record, so you ahve this:-

[tt]
Title1 Title2
Sht1 a
Sht1 b
Sht1 c
Sht1 y
Sht2 b
Sht2 c
Sht2 z
Sht2 0
[/tt]

Now do Data / Pivot Table and chart report, drag Title 2 into the ROW fields, Title 1 into the COLUMN fields and then drag Title 2 again into the DATA field, ensuring it is set to COUNT. Now just use Data / filter autofilter on the Grand Total column and filter for 1s.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[AlreadyLost]

Thanks. This works..

 

[kjv1611]

Thanks. This works..

Which sollution?