Arranging Data

Hi

I have searched Google for the answer and have not found anything to resolve my problem. I assume there is a simple answer but I have not come across it yet.

We receive a report from other software in excel format. What I need to do is create a method where if i receive the updated data at the end of the week it automatically arranges itself into a spreadsheet I can work with.

I have attached a spreadsheet with what I need. The DATA tab is exactly what the software exports. The Sorted Tab is the minimum requirement so that I can then do a Pivot Table or just work with the information.

The item that has me stumped is that the Employee name is in the same column as the date he / she worked. I need the name in a separate column next to the dates in order to set up an IF THEN formula.

My thoughts are there should be something that can be done that could maybe identify the names and then move them to a new column?

I will need to do this on a weekly basis so am trying to automate the process.

Looking forward to some excel magic...

Could you use the comma in the name to identify them as such?

Please Login or Register  to view this content.

Thanks for the Suggestion Paul,

I am no Excel Fundi. My Problem is how would the formula know to stop with one names result and continue with the next names result.

In the Virgin format there is a name with lines of dates in the same column.

IE:

Alan
1
2
1
Peter
1
9
5
8
Zoe
8
4
5
6
9
4

So I want the result to be
Alan = 4
Peter = 23
Zoe = 36

if I could Auto Sum until he next name that would be great. But the number of numbers will change weekly. If I do this manually it will take me a week.

Thanks Glenn,

This is helping. I think I can now make the sheet do what I need.

Originally Posted by Glenn Kennedy

QSAlan: are you still using Excel 2003 or ealier? Either way, please amned your profile to show the EXCEL version you are using. If you're using 2007+ - why still use the outdated .xls file type?

Hi Glen,

The software i get the information from exports in .xls.

Thanks for the assistance. I am still busy working on that spreadsheet but it seems to be coming together.

I am pleased to have found this group as I am working on Excel mostly these days and have allot to learn.

Looking forward to being involved in the group going forward.