Comparing 3 tables to a list of numbers, if match, return 4 columns worth of data on another tab

[classichemp]

I am a beginner at VBA and I believe what I require is a VBA code to help me solve what I am trying to accomplish. I have a list of numbers, and 3 tabs of tables. If any of the list of numbers match the number in any of those tabs, it will return the "number" along with the "Description", qty, and "cost" columns on another tab. in the end I should have a complete list of every time the compare list of numbers matches the numbers on any of those 3 tabs. Also, ideally, it would be nice to put the list of matching numbers in numerical order and add up the costs of all the numbers in a total cost section. Another possible addition I may or may not need is to consolidate all the same numbers that appear and add up the total quantity so that its 1 line per number with the total amount used.

I don't know where to start, tried to copy and modify sample code off the net but got no where.

 

[alansidman]

This will be a lot easier to resolve if you post a file to a third party location, ie. Box.Net or Dropbox.com. In that manner, no one here will have to retype all your data to test a solution that they have derived. Is there any particular reason that each tab has two sets of data? Why not have all the data in one range per tab?

 

[classichemp]

This will be a lot easier to resolve if you post a file to a third party location, ie. Box.Net or Dropbox.com. In that manner, no one here will have to retype all your data to test a solution that they have derived. Is there any particular reason that each tab has two sets of data? Why not have all the data in one range per tab?

Hi, The 2 sets of data on each tab is how our internal program works with our reports, i have no way of changing that unfortunately. I will work on getting the workbook uploaded to something for all. Thanks.

 

[classichemp]

This will be a lot easier to resolve if you post a file to a third party location, ie. Box.Net or Dropbox.com. In that manner, no one here will have to retype all your data to test a solution that they have derived. Is there any particular reason that each tab has two sets of data? Why not have all the data in one range per tab?

Here is the link
TEST DATA.xlsx

 

[alansidman]

In the file attached,
1. For each range of data, I transformed the range to a table. They are named Table1...Table7
2. Each table was imported to Power Query in a new workbook.
3. Tables 1 to 6 were appended to each other in a new query
4. Once the append query was created, they were grouped by number and description.
5. The grouped query was merged (joined with an outer join) with the comparing number table.

The end result is shown below and all the Mcode can be reflected in the attached file.

Book2
	A	B	C	D
1	number	description	Sum Qty	Sum Cost
2	5	BBB	445.3333333	15
3	5	DDD	1254.5	25
4	5	CCC	362.3333333	45
5	5	AAA	104	20
6	10	LLL	30	200
7	10	III	1336.333333	150
8	10	KKK	10	100
9	10	JJJ	22	250
Merge1

PQ Append and Group.xlsx | Powered by Box

app.box.com

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.