Extracting account numbers

[howard]

I have old account numbers in Col F and new account numbers in Col G

The account number in Col F may appear more than once, but it has a new corrresding number in Col G (Sheet1) workbook BR_ACCNTS

I have a list of old account numbers in another worbook and would like a macro to extract the new account numbers from Col G that pertains to these

One would need VBA code to do this as I may have foe instance account 4000 appear 4 times in Col F, but the new account numbers are unique for eg 114250, 114650, 114814, 114975 may all relate to account 4000

Your assistanceis mist appreciated

See example of account nubers that I want extracted as well as a sample of old and new account numbers

Excel Workbook
	A
1	4000
2	4000A
3	4001
4	4001A
5	4200
6	4200A
7	4002
8	4002A
9	4003
10	4003A
11	4004
12	4004A
13	4005
14	4005A
15	4006
16	4006A
17	4007
18	4007A
19	4008
20	4008A
21	4009
22	4009A
23	4015
24	4015A
25	4018
26	4019
27	4020
28	4039
29	4100
30	4100A
31	4101
32	4101A
33	4108
34	4108A
35	4109
36	4109A
NV COS

Excel Workbook
	F	G
221	4000	133004
222	4000	133005
223	4000A	133006
224	4000	133007
225	4000	133008
226	4000	133009
227	3003	133020
228	3003D	133021
229	3003K	133022
230	4003A	133023
231	4003	133024
232	4003	133025
TB CONSOLIDATION

 

[Oeldere]

With Vlookup and countif.

I added an excel file.

Please reply

See the link to a dutch free site.

2014-09-04 (o) mrexcelforum.xls downloaden

 

[howard]

Thanks for the help, but its nort giving me the correct answers

for eg Col A 4000 to extract 133004, 133005, 133007, 133008, 133009 as these all relate to a/c 4000 which is an old account number

 

[Oeldere]

If you get the idea of what my solution is, you will be able to amend that to your own need.

Since it is not clear (at least not to me) how the old numbers are related to the new nembers, I took a guess and filled the data with my guess.

 

[konew1]

You don't make it clear how you want the result presented.
Here is a possible pivot table solution

Excel Workbook
	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Old	New
2	4000	133004
3	4000	133005
4	4000A	133006
5	4000	133007
6	4000	133008
7	4000	133009
8	3003	133020
9	3003D	133021
10	3003K	133022
11	4003A	133023
12	4003	133024
13	4003	133025
14
15
16	Sum of New	Column Labels
17	Row Labels	133004	133005	133006	133007	133008	133009	133020	133021	133022	133023	133024	133025
18	3003							133020
19	4000	133004	133005		133007	133008	133009
20	4003											133024	133025
21	3003D								133021
22	3003K									133022
23	4000A			133006
24	4003A										133023
Sheet3

 

[howard]

Thanks guys for the help. The pivot table will help tremendously