Sorting a spreadsheet of data that is consistantly disorganised into organised columns

[[email protected]]

Hi there,

I have a spreadsheet of data where the data is consistantly spread over several cells and rows and I need to sort the data into consequtive columns and rows on a new worksheet. The best way I can explain is by probably giving you the spreadsheet.

You will see the data on the Poly sheet has data consistanly spread over the same consistant columns and rows and I need to get the relevant data onto the Sheet 1 worksheet in consequtive Columns and Rows using a function.

Any suggestions.

Many thanks.

Stewie.

[Dave H9]

Hi stewart,

Try the attached.

Dave H

[[email protected]]

Thanks Heaps Dave,

You got it first time.

I tried all sorts of things but just couldn't seem to crack it.

Thanks again.

BTW. I used to live in Manchester. Moss Side, Levenshulm and then Chorlton !

[Dave H9]

Your Welcome,

I'm orig from Scotland! If you've sampled the delights of Manchester what on earth took you to Oz!!! lol

Regards

Dave H

[[email protected]]

Travelled here as a backpacker in 1992 - 1993 and fell in love with the place took me 5 years to get back but now I'm here for good.

BTW. When I try to copy the formulas onto the original spreadsheet the cell formulas change as expected but the results are all the same as the row I copied ddown Sheet1?

Any Clues?

[Dave H9]

Have you added the helper column (H in spreadsheet I posted)? It is needed to increment each row. If so is calculation set to automatic?

PS I spent some time in Oz backpacking in late 90's but couldn't take to the place.

Dave H

[[email protected]]

Yeap I had the "Helper" column added I just didn't have the auto calc set to "ON"

Looks better now thanks.

I was the total opposite when visiting Oz. I couldn't wait to get back.

[[email protected]]

Just noticed Dave that some of the dates on the "Poly" tab are formatted as dates and some as general. The ones that are formatted General resolve OK on the "Sheet1" tabs however the ones formatted as Date do not.

CA1 Steven Patrick Archdall Male 22/03/1968 18/07/2012 Casual POLY - SITE
CA2 Clayton Baker Male 22/03/1977 27/01/2010 Casual POLY - SITE
SA1 Darren Barrett Male 28167 40946 Salaried POLY - OFFICE
CA3 Michael Cameron Male 28311 20/12/2010 Casual POLY - SITE
SA3 Mark Cestaro Male 30328 40727 Salaried POLY - OFFICE

[Dave H9]

Try the following (this would go in cell E2 of the original -

Please Login or Register  to view this content.

You will need to apply this for all the columns returning mixed format dates basically adding "TEXT(*orig formula*,"dd/mm/yy")

Dave H