Find common values, Consolidate data and create Worksheet with difference

**hnpsingh** · 11-20-2012, 10:45 PM

Thanks you for the forum, can i get help with a problem that is there, also sorry for my bad english.

I have 2 worksheets output by our accounts department, Companies and Orders, which has detail for the companies and orders. The format of the data will not change only number of rows change for the reports. This is only sample data.

I am try to consolidate these two by matching company ID and Company Name for two worksheets and then putting the found data in Consolidated, in the format show in Consolidated worksheet.

Basically match company id and company name, Column A and B of Companies match with Column B and D of Orders, and if they match put the data in Consolidated and if it doesnt then put data in No Order if no orders found for the company and put in FollowUp Order if there is order but no company there in Companies.

The format for the combined data is in Consolidated. No Order and Followup for Order are just copy and paste of the no found records.

I do this manual work and takes a lot of time and am good with excel but not with macros and programming.

Can someone help please? If more clarification needed please ask and I will answer. Sorry for my english again.

I have attach a sample workbook with sample data.

**HaHoBe** · 11-21-2012, 03:22 PM

Hi, hnpsingh,

you could use a loop for all the company ID and Names from Sheet Companies and either use Autofilter to check if there is dat for that company and tehn decide what to do next. I wonder why you have one ID but multiple Company names. Perhaps you could give me a hint about that?

Ciao,
Holger

**hnpsingh** · 11-21-2012, 03:47 PM

hi Hahobe,

Thank lot for helping me.

The company ID is unique to each company in our system but each company can have different branches and offices that why names are different. that why we need to match both the id and name and if they match put the info together in the consolidated sheet.

Also in the Followup on Order tab, the id 1999 has an order but no company present by showroom name. so this report helps find difference or missing info.

what i try to do is match company id and name and if present consolidate the data otherwise copy the data in the difference sheet.

If need more information please let me know. My english not very good but will try to explain.

**HaHoBe** · 11-22-2012, 01:23 AM

Hi, hnpsingh,

I actually do have a small problem with your last request for placing orders into Follow up for Orders from Orders if the ID or Name are not in Company. I could best work around that if we plcaed a makr in Sheets Orders for all those orders which have been placed into Consolidated. The other way would be to check each order number with Consolidated via WorksheetFunction.CounIf to decide whether to put this into Follow Up. This section is missing (waiting on your response for that).

Code goes into a normal module and needs to be started from there (either place the cursor into the procedure and press F5 for complete, F8 for Step through) or start from Excel by ALT+F8, choose macro and run:

Please Login or Register  to view this content.

Ciao,
Holger

**hnpsingh** · 11-22-2012, 04:39 AM

Hello Hahobe,

Thanks so much for help. If you can check to see if it is placed in consolidated or no. and if not present then if you can put in Follow Up for Orders, that should solve the issue.

What needs to go in the Follw up Order worksheet is the rows/data from the Orders taht is not moving into consolidated. is this the info you looking for?

Also when i run macro, column G and H in No Order worksheet show up as #N/A ! Is this an errror?

Really thankful to you for this help.

**HaHoBe** · 11-22-2012, 12:21 PM

Hi, hnpsingh,

originally I didn´t want to loop thorugh all the orders again which may be a bit time consuming if the list is longer. But it´s okay for the sample here and should be in a reasonable time span.

Regarding the #N/A: this will happen any time some if I want to write to 8 cells but just have 6 cells of value - I didn´t recognize the figures properly this morning, I´m sorry about that.

The amended code looks like this:

Please Login or Register  to view this content.

Ciao,
Holger

**hnpsingh** · 11-23-2012, 04:49 AM

Hi Hahobe,

Thank a lot for helping with this.

I ran a sample with the macro but its not working for some reason.

when i run the macro in the attached sheet: example:
only SB corp show up in No Order, even Satya Corporataion has no orders.

In Consolidated:
Company Id is 20120210 and name is Farsi Corp but id 20129000, 20120300 and 20120400 also show up even they dont have company by that name in the companies worksheet. Same for Sunayana Corporation.

Can you check in attached worksheet, with a different sample data.

Sorry to bother and thanks you for the helping me with this. I think we almost there but missing somethins in the logics.

**HaHoBe** · 11-24-2012, 02:51 AM

Hi, hnpsingh,

go to Sheet Orders, apply the Autofilter for Row1 and use as Criteria in B1 20120300. For what I take care of is unique IDs while in the sheet you´ll find Companies Sunayana, Sahu Traders and Farsi Corp. This is different from the sample you provdied before. So the logic applied when writing the code according to the first sample must fail on the second. I´ll think about that but it may take some time.

Ciao,
Holger

**hnpsingh** · 11-24-2012, 04:03 AM

Hi, Hahobe,

Thank for the helps. This is a sample from some of the data that used specially the first 3 column. You can take time if need anything from me plese let know.

**HaHoBe** · 11-24-2012, 11:41 AM

Hi, hnpsingh,

as by your last posts I altered from searching for the ID to searching for the name and checking if the ID in the row the name is found is identical. This will put Companies Sunayana (20120210) and Jai Bharat (20121000) into Considated, SB Corporation (20112021) and Farsi Corp (20120210) into No Order and all the other companies into Follow up.

Please Login or Register  to view this content.

Ciao,
Holger

**hnpsingh** · 11-24-2012, 02:14 PM

Hi Holger,

Thanks for the helps. But there are orders for the company Farsi Corp (20120210) on row 19,20,21,22 in Orders worksheet so them should be in consolidated to. And there is no orders for 20120300 Satya Corporation, so it should be in No Orders.

Sorry to confuse and make you work so much. Can you check see.

**HaHoBe** · 11-25-2012, 06:24 AM

Hi, hnpsingh,

maybe I took a wrong turn when starting on the code. Due to my experience any ID I use is unique (which really means that) and may be updated for different other things (say a 9-digit number with follow-up for different locations).

If I understand you correctly you´re telling me that you have one ID showing different company names (that´s what gets me messed up) or a company may have different IDs (but which to check for on Orders and to decide where to place the order/the company). If you have an ID on Companies which will not meet the first entry on Orders - what to do?

What is the leading item to sort things on: ID (you have both Sunayana and Farsi Corp sharing 20120210) or Company (Sunayana showing IDs 20120210, 20120300 and 20120400 but only the first is on Companies)? The code was developed relying on ID but I pretty much doubt that by now.

Ciao,
Holger

**hnpsingh** · 11-26-2012, 01:14 AM

Hi Hahobe,

Sorry for making confusion for you.

Company can have different ID's and that why when both the ID and company match we put them in consolidated. So Company and ID both should be considered to match.

If it is only present in Company and not in Orders they go in No Orders.

And if it is only present in Orders and no info in Companies they go in follow up for orders.

For my company, only the data in consolidated means valid orders, we call and verify the company or order in No order or Follow up for Orders.

Once they match and report comes out next time and we run this macro it should show up in Consolidated.

So the goal is that No Order and Follow up for Order should be empty at the end and nothing be missing.

**HaHoBe** · 11-27-2012, 04:12 PM

Hi, hnpsingh,

please have a try on the altered proceeding for the following macro (using Autofilter instead of Find):

Please Login or Register  to view this content.

Ciao,
Holger

**hnpsingh** · 11-28-2012, 04:34 AM

Hello Holger.

Thanks very very much, you saved me lots of trouble and work. Really thank you much. Once last question about this.

I try to write header information for Consolidated but it gives me syntax error. This is the code that I use

Please Login or Register  to view this content.

Me think the Value line is too long and I get syntax error as it more than 255 characters i think. Can you help with this last issue

Thanks veryy muchhhh !!!!

**HaHoBe** · 11-28-2012, 02:58 PM

Hi, hnpsingh,

maybe try the code this way:

Please Login or Register  to view this content.

Ciao,
Holger

**hnpsingh** · 11-28-2012, 04:22 PM

Thank you very much. you best here.

Find common values, Consolidate data and create Worksheet with difference

LinkBack

Thread Tools

Rate This Thread

Display

Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Re: Find common values, Consolidate data and create Worksheet with difference

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions