Code To Look For Number On Sheet 2, When Found Insert Row & Add Number Next To It

[Dazzawm]

Hi, I have sheet 1 with 3 columns of data. The code needs to look for the number that is in column C on sheet 1 on sheet 2. When it is found I need a row inserted below on sheet 1 with the numbers that are next to that number on sheet 2. The example below will explain better.

Excel Workbook
	A	B	C
10	9	AR145161001	EFAR8008
11	10	AR145161001	ECAR5012
12	11	AR145161001	EXAR9001
Sheet1

Excel Workbook
	A	B	C	D
2	1	EFAR8008	EMG002	Gasket
3	2	ECAR5012	EMG002	Gasket
4	3	ECAR5012	EMG171	Gasket
5	4	EXAR9001	EMR031	Rubber
6	5	EXAR9001	EMR113	Rubber
7	6	EXAR9001	EMR130	Rubber
Sheet2

Excel Workbook
	A	B	C	D
10	9	AR145161001	EFAR8008
11	9	AR145161001	EMG002	Gasket
12	10	AR145161001	ECAR5012
13	10	AR145161001	EMG002	Gasket
14	10	AR145161001	EMG171	Gasket
15	11	AR145161001	EXAR9001
16	11	AR145161001	EMR031	Rubber
17	11	AR145161001	EMR113	Rubber
18	11	AR145161001	EMR130	Rubber
Sheet1 Result

As you can see in the 3rd table is the result on sheet 1. It has found wherever the number is on sheet 2 and add the data that is next to it in columns C & D on sheet 2. Please help and can get big brownie points from my Director!!!

 

[Dazzawm]

Forget the part where I said it copied some rows several hundred times I used it on the wrong file, but it does only about 10%. I think the first code is nearer the mark but it just keeps freezing.

 

[bertie]

Ok, this isn't going to work using Excel.
You have 73k records on sheet1 and 19k on sheet2.
We need a database.

Open Access, with a blank database.
Click the ExternalDate tab and select Excel as the source.
Browse for the file and click OK.
Select Sheet1 as the source and click Next.
Tick first row contains column headers and click next.
next
next
next
finish
close.

Repeat and import sheet2.


Select the Create tab.
Select Other=> Query Design
Add Sheet1 and Sheet2 tables and close the dialog box.

Select the PART in sheet1 and drag it over to the PART in sheet2 - this is the relationship between the tables.
Double click on
Sheet1 - index
Sheet1 - system code
Sheet1 - part
Sheet2 - CatCode
Sheet2 - Desc

As you double click you will see these populate the table below the split screen.

On the Results tab click Run, the red exclamation mark icon.

You can right click the Query and export the results.

 

[Dazzawm]

That does work but not quite as I need. I need the parts underneath whereas the database puts them in a column next to it.

 

[Dazzawm]

This is th result using access

INDEX	SYSTEM CODE	Sheet1_Part	Sheet2_Part	Desc
1	ANAMB171001	EXAN2020	EMB023	SPRING
2	ANAMB171001	EXAN2020	EMCLAMP	48mm
3	ANAMB171001	EXAN2020	EMCP024	MANIFOLD CLAMP
4	ANAMB201001	EXAN2020	EMB023	SPRING
5	ANAMB201001	EXAN2020	EMCLAMP	48mm
6	ANAMB201001	EXAN2020	EMCP024	MANIFOLD CLAMP

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

But what I need is

`	SYSTEM CODE	Sheet1_Part	Sheet2_Part
1	ANAMB171001	EXAN2020
2	ANAMB171001	EMB023	SPRING
3	ANAMB171001	EMCLAMP	48mm
3	ANAMB171001	EMCP024	MANIFOLD CLAMP
4	ANAMB201001	EXAN2020
5	ANAMB201001	EMB023	SPRING
6	ANAMB201001	EMCLAMP	48mm
6	ANAMB201001	EMCP024	MANIFOLD CLAMP

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Dazzawm]

Update: I did run the code and it did complete but it took most of the night with a few errors which I corrected manually so for now I have the result I need. I would however appreciate it if you can think of a way to speed up the code as it will be needed to be used quite a lot in the future. Thanks for your time Bertie.

 

[bertie]

Hi Darren,
I am away for Easter and it will be Sunday evening at the earliest before I can look a your stuff again.

Have you tried putting the result of the database query into a pivot table?

Anyway, I assume the database query has compacted your data down to a more manageable size. You have my email address. Can you post me a sample of data from the database query.