Keep unmatched data when merging 2 sheets

Question

I am able to Merge two sheet into one but when I do any unmatched data disappears from my new sheet. How do i keep the unmatched data? I am using excel 2016. I use the "import from other source" then "import from microsoft query".

Answer 1

Hi,

Is this your expected result (see table in range A9:H16)

Answer 2

Would a formula work instead of merging?

By adding an on hand column then putting a formula:

=IF(ISNA(VLOOKUP(B3,'sheet 2'!A3:C7,3,FALSE)),"",VLOOKUP(B3,'sheet 2'!A3:C7,3,FALSE))

it will just bring the on hand over and not mess anything up

would this solution work better for you?  If it does please mark solved.  Thank you

Answer 3

Would a formula work instead of merging?

By adding an on hand column then putting a formula:

=IF(ISNA(VLOOKUP(B3,'sheet 2'!A3:C7,3,FALSE)),"",VLOOKUP(B3,'sheet 2'!A3:C7,3,FALSE))

it will just bring the on hand over and not mess anything up

would this solution work better for you?  If it does please mark solved.  Thank you

Answer 4

This is matching my inventory against what is actually on the floor and what our system says. 

Sheet1

Category	ItemNumber	ItemName	Cost	AvgCost	TOTALQTY
cd	100-c	a	1	1	100
bk	202	b	2	2	55
bk	305	c	3	3	23
dvd	1001-d	d	4	4	74
misc	8316	e	5	5	1

Sheet2 

ItemNumber	Description	On Hand
100-c	a	101
288	g	45
305	c	23
1001-d	d	75
8000	h	89

I want both merged to one sheet which I can do but I still want all unmatched data to stay. So it will look at both sheets and recognized the itemnNumbers are the same so put them on the same line. This will make it easier for me to compare then show the variance between the two.

Answer 5

Hi,

Show the datasets and the expected result.