Excel imported data

[davea300]

Hi

I have an excel sheet that I've imported into an access 2007 table. It has a field with the user name in it but every so often a location address appears in this column meaning that the users who appear below the address are based at that location. What I need to do is create another column and copy that location address for all applicable users but since the address changes every few rows it's a bit more complicated than I thought. The good thing is the location address always starts with the word "Location:" as below so perhaps that makes it easier, any suggestions. Here's how the column looks:

USER

Location: 1 anywhere street
Homer Simpson
Marge Simpson
Dave Jenkins
Location: 34 over there street
Bart Simpson
Big Jake
Lance Jolly
Mrs Murphy
Mr Murphy
Location: 56 home street
Mr Big
Mr Small
Mr Tall
Mr Greedy

Here's how I want it to look:

LOCATION | USER

Location: 1 anywhere street | Homer Simpson
Location: 1 anywhere street | Marge Simpson
Location: 1 anywhere street | Dave Jenkins
Location: 34 over there street | Bart Simpson
Location: 34 over there street | Big Jake
Location: 34 over there street | Lance Jolly
Location: 34 over there street | Mrs Murphy
Location: 34 over there street | Mr Murphy
Location: 56 home street | Mr Big
Location: 56 home street | Mr Small
Location: 56 home street | Mr Tall
Location: 56 home street | Mr Greedy

 

[Solo712]

Hi

I have an excel sheet that I've imported into an access 2007 table. It has a field with the user name in it but every so often a location address appears in this column meaning that the users who appear below the address are based at that location. What I need to do is create another column and copy that location address for all applicable users but since the address changes every few rows it's a bit more complicated than I thought. The good thing is the location address always starts with the word "Location:" as below so perhaps that makes it easier, any suggestions. Here's how the column looks:

USER

Location: 1 anywhere street
Homer Simpson
Marge Simpson
Dave Jenkins
Location: 34 over there street
Bart Simpson
Big Jake
Lance Jolly
Mrs Murphy
Mr Murphy
Location: 56 home street
Mr Big
Mr Small
Mr Tall
Mr Greedy

Here's how I want it to look:

LOCATION | USER

Location: 1 anywhere street | Homer Simpson
Location: 1 anywhere street | Marge Simpson
Location: 1 anywhere street | Dave Jenkins
Location: 34 over there street | Bart Simpson
Location: 34 over there street | Big Jake
Location: 34 over there street | Lance Jolly
Location: 34 over there street | Mrs Murphy
Location: 34 over there street | Mr Murphy
Location: 56 home street | Mr Big
Location: 56 home street | Mr Small
Location: 56 home street | Mr Tall
Location: 56 home street | Mr Greedy

Hi Dave,
sure, you can do it with VBA code if the protocol holds for the whole column. But unless this is a humungous sheet I would say separate the locations and names into columns in Excel and then import, given that you asking to create code for a very specific, non-repeat occasion.

Best,
Jiri