I am looking for a way to copy data between sheets based on multiple ID prefixs

[RaghulSekar]

I have a list of list of around 800 cities with prefixes as shown below
CITY Prefix
CITY1 1
CITY2 389
CITY3 244
CITY4 12
CITY5 123
CITY6 76
CITY7 745
CITY8 74
CITY9 113
CITY10 22
CITY11 99
CITY12 999

Now I have to make a 4 digit prefix sheet from 1000 to 9999 and map them. For ex CITY1 I have to copy and map to 1000 to 1999 as everything follows 1 is prefixed for CITY1.

But the problem is I have to Map CITY4 to 1200 to 1299 because CITY4 prefix is 12 so all 4 digits of CITY4 should be copied and mapped to 1200 to 1299. So this will be sub map under 1000 to 1999 but only for the range 1200 to 1299

Likewise I need to do for 800 cities one by one and many carry multiple mapping ranges nested.
Is there any way to achieve this without doing them one by one which can take weeks.

 

[Micron]

I think you'll have a better chance of getting a solution if you post some sample data rather than try to explain it in words. F'rinstance, does 113 map to 1100 to 1199 or to 1130 to 1199? When you explain it, sometimes the mapping is one digit (1) and sometimes it is 2 (12). Maybe sometimes it's 3 or more?
You can easily copy data and results expected from a mocked up spreadsheet.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: I am looking for a way to copy data between sheets based on multiple ID prefixs
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[RaghulSekar]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: I am looking for a way to copy data between sheets based on multiple ID prefixs
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thanks for letting me know. I will keep in mind if I cross post in future. For now only at one place.
I am looking for a way to copy data between sheets based on multiple ID prefixs

 

[RaghulSekar]

I think you'll have a better chance of getting a solution if you post some sample data rather than try to explain it in words. F'rinstance, does 113 map to 1100 to 1199 or to 1130 to 1199? When you explain it, sometimes the mapping is one digit (1) and sometimes it is 2 (12). Maybe sometimes it's 3 or more?
You can easily copy data and results expected from a mocked up spreadsheet.

HI Micron, Thanks for the response, Here 113 range should cover from 1130 to 1139, but if there is another CITY with prefix say 11, it should cover from 1100 to 1199, like all numbers on 11**.
same applies if its 1 digits, maximum number considered are from 4 digits from 1000 to 9999.
So there will be more nested prefixes every time I have to organize say around 800 prefixes in the list on 1000 to 9999
Attached a screenshot, I am not able to install XL2BB. here CITY1 is from 1*** so from 1000 to 1199 all organized under CITY1, but CITY4 is from 12 so 1200 to 1299 nested further under CITY1.
Like this I have to organize around 800 cities one by one. Any help to do this would save me a lot of time

 

[RaghulSekar]

The solution for the query has been achieved in another forum , which the link was shared above.
Thanks Micron for your assistance to trying to help out
Really Appreciate it