Report re-formatting - Vlookup+Index+offset - HELP!

[Armitage2k]

Hello,

I would like to request some help in coding a VBA module please.... I have attached a sample of a HR report that my company pulls from the attendance system. Now, this report looks like and does all I need, but the formatting does not allow me to easily pull statistics and further manipulate the data in excel. What I would need is a macro that I can run upon import of the sytem report, which helps me to reformat and align all the data on the sheet.

Particularly, this needs to be done:
* Column J holds the staff position, while all clocking-in / -out data is captured in the line below. I need this aligned in the same row.
* One staff can have multiple clocking records over a certain period of time. Every time there is a clocking record, I would like to have the staff name, id and position copied and shown in the same row.

I attached a BEFORE and AFTER sheet to highlight what I would need changed. The main challenge I am facing here is that I have around 600 employees on this report and would need to data arranged for every single one of them. I did play around with INDEX(), OFFSET() and VLOOKUP via excel functions, but just dont get anywhere since the format is too dynamic for me in order too use static formulas...

I would be more than happy to buy whoever fixes this for me a beer or two. or ten, depending on how much you can drink... (university students are exempt from this clause)

THANKS!
A2k

test.xls

[humdingaling]

will this do?

[humdingaling]

FYi you have a double up in your after sheet which threw me off
staff 2 is repeated twice in after but not in before

[Armitage2k]

Thanks a lot for your efforts!

The script is slowly getting me there, but unfortunately there seems to be something wrong with the offset as it deletes the staff's ID, name and position... I tried your macro with my whole data, but it seems quite a difficult task since the layout is so damn irregular. Lucky though, I have found an option to export a DELIMITED report from the HR system which cleaned up the format a little bit. Now however, the following steps would be necessary to have everything aligned:

* go through column A and identify if cell value is a number between 1 and 25, respectively make sure the cell value is NOT a date, NOT text and NOT number + text.
* if cell value is a number, use end(xldown) until the paragraph is finished, and then cut and paste to column D via offset of 1x row up starting from the first entry of the paragraph.
* continue looping through column A and once done, delete entire row of cell in column D is empty

Is something like this possible?
Thanks a lot!
A2k

A2k (Rev2).xls

[humdingaling]

Please Login or Register  to view this content.

ok this codes down the list in column A to see if it IsNumber (different results to isnumeric)

if it is
slide it across 3 columns
and copies down A to C
repeat until end

then goes from bottom up
and deletes and lines where D:L is blank

[Armitage2k]

You are a lifesaver sir, have a VB on me

Cheers!
A2k

[humdingaling]

not a problem
glad to help

Cheers
Hum