Extracting account numbers

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,784
Office Version
  1. 2019
Platform
  1. Windows
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
14000
24000A
34001
44001A
54200
64200A
74002
84002A
94003
104003A
114004
124004A
134005
144005A
154006
164006A
174007
184007A
194008
204008A
214009
224009A
234015
244015A
254018
264019
274020
284039
294100
304100A
314101
324101A
334108
344108A
354109
364109A
NV COS


Excel Workbook
FG
2214000133004
2224000133005
2234000A133006
2244000133007
2254000133008
2264000133009
2273003133020
2283003D133021
2293003K133022
2304003A133023
2314003133024
2324003133025
TB CONSOLIDATION
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,784
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
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

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
You don't make it clear how you want the result presented.
Here is a possible pivot table solution

Excel Workbook
FGHIJKLMNOPQR
1OldNew
24000133004
34000133005
44000A133006
54000133007
64000133008
74000133009
83003133020
93003D133021
103003K133022
114003A133023
124003133024
134003133025
14
15
16Sum of NewColumn Labels
17Row Labels133004133005133006133007133008133009133020133021133022133023133024133025
183003133020
194000133004133005133007133008133009
204003133024133025
213003D133021
223003K133022
234000A133006
244003A133023
Sheet3
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,784
Office Version
  1. 2019
Platform
  1. Windows
Thanks guys for the help. The pivot table will help tremendously
 

Watch MrExcel Video

Forum statistics

Threads
1,130,117
Messages
5,640,206
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top