Tidy up of data within Excel Report

[mralmackay]

Hi,

Please can you help with the following problem that I've got to sort.

I have data listed, as below, which I need to 'tidy up' to appear as a
flat file.

DATA EXTRACT:
G-ICPR-7203--ZTEA1GL00 Expense policy
kwcx301 smith, John
kcrq254 rogers, nikki
G-ICPR-7203--ZTEA2GL00 expense manager
kgjx668 frame, Shereen
kwsq896 power, max
krdf108 Archer, Michelle
kxfq690 frame, Stephanie
G-ICPR-7203--ZTEA3GL00 Deputy role
krzx217 hough, John
kzkn268 mackay, garry
kdtz927 birch, lilian
G-ICPR-7203-G7203E01 general Role
kwcx301 fishbourne, ronald
kcrq254 abbott, giha
kzcl750 Abbott, Ramil
kwbs802 monk, chip
kgwv200 dibley, duane

The title of the role that people belong to exists in Column A
(G-ICPR-7203--ZTEA1GL00), Description in Column B (e.g. Expense policy)
and then the users who have them roles are on the next row down (ID in
Column C and Name in Column D).

Is there anyway that I can restructure this data automatically (there
is just over 20,000 rows) so this appears:

Column A: Column B: Column C:
ZTEA1GL00 kwcx301 smith, John
ZTEA1GL00 kcrq254 rogers, nikki
ZTEA2GL00 kgjx668 frame, Shereen
ZTEA3GL00 krzx217 hough, John
G7203E01 kwcx301 fishbourne, ronald
etc.. etc... (haven't done the whole example from below, but this shows
what I need).

You can see in the above (column A) that I also only need any part of
the data string after the last - within the data string.

Thanks in advance for your help as always, Al Mackay
([email protected])

 

[John Vinson]

Please can you help with the following problem that I've got to sort.

I have data listed, as below, which I need to 'tidy up' to appear as a
flat file.

Is this in Access, or in Excel? If (as the subject suggests) you're
working in Excel, you would do better to post your question to an
Excel newsgroup.

John W. Vinson[MVP]