Compare Quantity by Unique ID in two worksheets/Workbook

Dear AliGw,
Thanks alot. I believe i am able to attach successfully

Hi,

attach at least your own sample workbook rather than one from another thread
- with a complete crystal clear explanation of the need - as that's very confusing …

… or you are enough confident with your Excel / VBA skills to amend any generic code some helper may post ?

All I can do is to ask a VBA beginner kid to rewrite the uggly initial code - as an Excel basics lockdown exercice -
in order to create the colored data in Changes worksheet but it seems that is not what you are expecting for …

Originally Posted by Marc L

Hi,

attach at least your own sample workbook rather than one from another thread
- with a complete crystal clear explanation of the need - as that's very confusing …

… or you are enough confident with your Excel / VBA skills to amend any generic code some helper may post ?

All I can do is to ask a VBA beginner kid to rewrite the uggly initial code - as an Excel basics lockdown exercice -
in order to create the colored data in Changes worksheet but it seems that is not what you are expecting for …

Hi Mark,
I believe I wrote "I found some similar routine" to indicate that I found this routine. I didn't delete any wording in the code so its is easily understandable but the sheets inside 'yesterday and 'today' are amended as per my need.
"Material No' and 'Quantity' are getting compared between sheets 'Yesterday and 'Today' same as showed above in my post 'Request' and 'Ordered'.
'Changes' worksheet is similar to my expected 'Remarks' sheet but with the following differences.
1. It does add values before comparing 'based on unique id.
2. Columns 2 is fixed for comparison which I am looking for dynamic.
3. Rather than color change I am looking for a write-up

Let me know if its clear or require more explanation. Thanks for your response.

Maybe it could be usefull you clearly elaborate with details points #1 & 2 …

Originally Posted by Marc L

Maybe it could be usefull you clearly elaborate with details points #1 & 2 …

Marc L

In reference to the Image above, I am looking to compare two sheets with regards to their total quantity as per their ID.

1. I am looking for a routine to add quantities in the sheet (Order & Request) under the same ID before start comparing.
2. Report differences as either additional quantity, reduce quantity, not available in a separate sheet ("Remarks"). Refer Image-3 above

Attach your own sample workbook well reflecting the before state and the expected result …

Or are you able to fit any VBA generic code 'cause of your initial attachment which not relates your need, even its code !
If not it will be just a waste of time in particular for any helper …

Originally Posted by Marc L

Attach your own sample workbook well reflecting the before state and the expected result …

Or are you able to fit any VBA generic code 'cause of your initial attachment which not relates your need, even its code !
If not it will be just a waste of time in particular for any helper …

Marc L,

Kindly find excel file attached "Compare Quantities - Expected Result".

I am looking for the following.
1. Two sheets are compared "Requested" & "Ordered" for their quantities based on their ID regardless of the column number they are located it.
2. Remarks sheet report differences after summing up the values in both sheets (As per ID) either as "ADDITIONAL", "REDUCE QUANTITY" OR "NOT AVAILABLE" - See Attached Excel File.

Thanks

As nobody answers so this is the situation on my side : your last attachment does not match your initial post pictures …

I send it to a VBA beginner kid as an exercice
- easy just with Excel basics and the Macro Recorder as you can do yourself -
and he just answers « how can it be possible to make such a bad worksheet design ?! »
Anyway he succeed to get the expected result.

But I won't even start to clean his code generated by the Macro Recorder
without a confirmation that the last attachment is the real layout
'cause the code may not work well for another design like your initial pictures …
After confirmation I will post the code and I will not modify it if you forgot anything
so well think about your need and the real layout before answering …

Marc, I dont see any difference in my attached sheet 'Compare Quantities - Expected Result' and Image posted above except one line remark statement.

Regarding your beginner kid I really want to know what is bad worksheet design ?

I put ID and Quantity in different columns in two worksheets as most of the time the two sheets that we receive for comparison doesn't have the COLUMN fixed for ID & Quantity in terms of Location.

Would like to hear back from you on how this design is bad.

Usual good design in headers starting in A1, no blank row between headers and data, …

So check your last attachment and if it does not reflect E X A C T L Y the real layout
- which is o b v i o u s l y the case according to your initial pictures -
attach at least the good one or you are very confident with your Excel / VBA skills
to fit any code to your real workbook so are you able ?

For ID & Quantity columns : do you mean their positions change everytime in both source sheets ?
In this case no typo is allowed in headers names like in worksheets names !

Marc L,
Yes the position changes in both sheets and I am not expecting it to be attached with the column header name 'ID' rather can be selected as a range from InputBox.

I already worked out the remaining part of the VBA that is workable for me but the only tough part for me is to take 'column' range input as Input box. So user can do the following.
1. Select ID Column range which needs to be compared in the "Request" sheet.
2. Select ID Column range to be compared with in "Ordered" Sheet.
3. Select the Quantity range that needs to be compared in the "Request" sheet.
4. Select the Quantity range to be compared with in "Ordered" Sheet.

Jeff,
Thanks for notifying me and apologies for missing out. I posted a similar question Mrexcel today but when I trying to put the link I am getting the following error: You are not allowed to post any kinds of links, images or videos until you post a few times.

Please let me know if I am missing out on something. Thanks

Originally Posted by AliGW

Post the link with a few gaps in it, then you will be able to do it.

Thanks AliGW. Edited original post with a link. Hope its acceptable.

Originally Posted by rummaan17

I already worked out the remaining part of the VBA that is workable for me but the only tough part for me is to take 'column' range input as Input box

See Application.InputBox in VBA help, easy …

In case you need more help, after posting the expected links - a must be since the creation of this thread ! -
post at least your actual code - the same as links, should be in the initial post ! - with the code tags as per forums rules.

MarcL,
I have modified the code shared above to suit my needs. I am working with Application.Inputbox so that I can request two range input that forms part of an array, similar for other sheet before comparison.

I will post the code soon after tidying it up. Thanks