Compare 2 sheets and show balance

**anilpatni1234** · 07-08-2018, 10:07 AM

Hey Guys ,
I need help for a problem.

I am into small morgaging business.

Sheet "ENTRY" is the sheet where I enter CODE, customer name , and other details.
Sheet "Withdraw" range "A" is the sheet where I enter the codes of the items withdrawn by the customer.

Now I want to compare sheet "Entry" and "Withdraw" and show the remaining items with me in sheet "In Hand"

So in my sample sheet i have entered many codes in sheet "Entry" .
Some of the items have been withdrawn , which i have shown in sheet "WITHDRAW"

I just want the items which are not withdrawn in sheet "IN STOCK"

can anyone please help me?

I have attached the sample workbook

**anilpatni1234** · 07-09-2018, 02:19 AM

Anyone please help me

**AliGW** · 07-09-2018, 02:23 AM

Please mock up what you want to see in the IN HAND sheet - it is currently blank.

**anilpatni1234** · 07-09-2018, 02:59 AM

Hey,
Thank you so much for your response.

Sheet "In Hand" range "A" must be the codes which are not withdrawn.

Basically sheet "ENTRY" range "A" minus Sheet " WIthdraw" range "A"

I want to match range A of sheet "ENTRY" and "withdraw" and show the remaining in sheet "In hand"

**AliGW** · 07-09-2018, 03:04 AM

This tells me nothing useful:

Excel 2016 (Windows) 32 bit

	A	B	C
1	CODE	CUSTOMER NAME	ADDRESS
2	AS3
3	AS4
4	AS5
5	AS6
6	AS7

Sheet: IN HAND

I asked you to mock up what you want to see on that sheet. You have just provided an empty data table. Please do as you have been asked and then you will get a solution quicker.

**anilpatni1234** · 07-09-2018, 03:28 AM

Hey,

Sheet "In hand" range "A" is the data which i intend to find.

I have matched the 2 sheet and done it by hand.

I am looking for a formulae to get these.

I want to match range A of sheet "ENTRY" and "withdraw" and show the remaining in sheet "In hand"

I have updated the sample file. Please check.

**AliGW** · 07-09-2018, 03:32 AM

Thanks - I will have a look.

Are you using Excel 2007 or a later version? If the latter, you need to update your forum profile to show the current version you are using. Please do this now. Thanks.

**AliGW** · 07-09-2018, 03:37 AM

In A2 (adjust the length of thw arrays to suit the size of your data):

=IFERROR(INDEX(ENTRY!A:A,SMALL(IF(ENTRY!$A$2:$A$8 <> WITHDRAW!$A$2:$A$8,ROW($A$2:$A$8)),ROWS($A$1:A1))),"")

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once confirmed, drag copy down.

**anilpatni1234** · 07-09-2018, 03:59 AM

Please ignore this message.

Multiple uploads

**anilpatni1234** · 07-09-2018, 04:01 AM

Hey.

Thank you.

I am using Excel 2007

**anilpatni1234** · 07-09-2018, 04:02 AM

Also the formulae does not work .

Sheet "In Hand" must be showing me the codes of all those items which are not present in range A of sheet " withdraw"

But it does not work at the moment.

I have presses Cntrl Shift Enter as well. but its not working

**AliGW** · 07-09-2018, 04:21 AM

It's working fine here - I will try to attach it (although the forums are on the blink, so I may not be able to).

**anilpatni1234** · 07-09-2018, 04:25 AM

HEy.

I got the problem.

I am not getting the "{" and " }" even after pressing cntrl shift enter

**AliGW** · 07-09-2018, 04:27 AM

You are doing something wrong, then. Use my attachment. Do NOT attempt to type the curly brackets yourself - that will stop the formula from working.

**anilpatni1234** · 07-09-2018, 04:29 AM

Hey.

Thank you so much for your help.

I will use your attachment .

But i will try finiding the error as well.

Thank you so much for your time.

**AliGW** · 07-09-2018, 04:31 AM

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**anilpatni1234** · 07-11-2018, 04:29 AM

Hey Ali,

I encountered a problem.

I have attached my workbook.

I have put the formulae as discussed with you.

However its not working right now.

Also I have modified the formulae to entend range to 500000.

can you please help me

**AliGW** · 07-11-2018, 04:31 AM

Where is the updated file? I see no new attachment.

**anilpatni1234** · 07-11-2018, 04:32 AM

Internet problemm. Its uploaded . Pleas e check

**AliGW** · 07-11-2018, 04:36 AM

One of the ranges needs changing:

=IFERROR(INDEX(ENTRY!A:A,SMALL(IF(ENTRY!$A$2:$A$500000<>WITHDRAW!$A$2:$A$500000,ROW($A$2:$A$500000)),ROWS($A$1:A1))),"")

All ranges need to be the same size.

**anilpatni1234** · 07-11-2018, 04:47 AM

I have made the ranges of same size.

However its not showing me the results.

It was suppose to match sheet "Entry" and "Withdraw" and show the balance.

However its not working for me.

I have uploaded the workbook

**AliGW** · 07-11-2018, 05:19 AM

In what way is it not working? Explain what you mean.

No time now, sorry - just going out.

**anilpatni1234** · 07-11-2018, 05:38 AM

HEy Ali,

Thats okay.

Whenever you are free then have a look.

In sheet "Entry" range "A" there are 5 codes namely - AS1,AS2,AS3,AS4,AS5
In sheet "Withdraw " range "A" there are 2 codes namely - AS1 and As3

So sheet "in hand" must show only As2,As4 and As5 (as these are the items which are not withdrawn yet)

However if you see sheet "In hand" it stilll shows code AS3 which is already withdrawn.

Could you please have a look

**JeteMc** · 07-15-2018, 11:33 PM

Try the following:
1) Paste the following into cell A2 on the IN HAND sheet:

Formula:

Please Login or Register  to view this content.

2) Select to have the formula copied to the rest of the cells in the column.
Let us know if you have any questions.

**Luke Man** · 06-09-2020, 12:32 AM

Try
=IFERROR(INDEX(ENTRY!A:A,SMALL(IF(ENTRY!$A$2:$A$5000<>WITHDRAW!$A$2:$A$5000,ROW($A$2:$A$5000)-ROW($A$2)+1),ROWS($A$2:A2))),"")
Control+Shift+Enter

**Luke Man** · 06-09-2020, 12:32 AM

Try
In A2:
=IFERROR(INDEX(ENTRY!A:A,SMALL(IF(ENTRY!$A$2:$A$5000<>WITHDRAW!$A$2:$A$5000,ROW($A$2:$A$5000)-ROW($A$2)+1),ROWS($A$2:A2))),"")
Control+Shift+Enter

Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Re: Compare 2 sheets and show balance

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] compare two excel sheets and show missing data in a new sheet

[SOLVED] Getting the Check Register Balance to show on another Sheet

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions