Sorting columns by rows

Hi, if anyone could help me with this it'd be much appreciated and save a lot of time compared to doing it manually;

I have in row 1 the time in 30 minute intervals
In each column I have registration plates, so if someone was parked at 7am they will appear in that column.
Due to the way the data was recorded, it was entered into excel quite chaotically and as such if someone was parked for 2 hours it is difficult to see this, as in the 7am column they may be in row 2 and in the 7.30am column row 20...

If anyone has any ideas on how I could sort all the columns so that the data in each column remains in the column but becomes aligned so that every instance of X7Z 123 is in the same row it would be appreciated.

I'm not really expecting a solution to this as I've already checked in other places and been told it would require some extensive visual basic programming, but thought I'd check just in case.

Thanks

Attached is an image of how the data looks, I'd like 1 row dedicated to each individual reg ...

Using Excel 2003

Hi TransportPlanner. I have a macro I think will do this, I can tweak it for you. Please post up your workbook so I have valid data to test this against. Make sure the sample wb demonstrates a full set of columns.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

I'm not sure if this is what you are looking for but see the attached. For the restructured table, I first created a column of unique ID's (plates) and then used the formula in B20, dragged across and down. Is that what you are looking for?

Is this the output you'd expect?

Please Login or Register  to view this content.

I found a mistake in my formula (it should have matched SHG's results so I knew I was wrong). In B20, it should refer to column A not B like so
=IF(ISNA(MATCH($A20,A$3:A$16,0)),"",$A20)
That can be dragged across and down. Then if you select the entire range, it can sorted alphabetically.

Explaination. MATCH looks in the 7AM range (A3:A16) for a match from the unique id cell (A20). It will either return a number matching the row (e.g. if the match is in A3, it returns 1, A4, returns 2, etc) or it returns a #NA error if there is no match.
The ISNA(Match(...)) returns true if it finds an error (no match) and False if there isn't an error (is a match). If there's an error, the IF statement returns a blank (""), if there is no error, it returns the unique ID.

Hope that helped.

If Chemist's solution works for you, stick with that. I used VBA, and there's no need to jump into that end of the pool unless you reallly need to.

I think the "Remove Duplicates" is new with Excel 2007 (bummer). There are a couple of quick ways to do this. I think the easiest (no VBA, no Formulas) is to first take all of your values and place them in a single column. Put a header on that column (e.g. Unique IDs) and select the entire range including header. Go to Advanced Filter, select "Copy to another location" (input the first cell of a blank column) and "Unique records only." Click okay and you'll get a list of unique records. Does that work for you?

Thank you very much, I was going wrong by thinking I could select it as it was (multiple rows and columns) rather than putting it all into one column first. I shall change this thread to solved now, thanks :D

Only other thing now I'm doing it; how does excel know to ignore the field 'Unique IDs' - I've tried that unique filter with and without it, both work - and yet the one with Unique IDs doesn't put Unique IDs into the sorted data. Would probably be useful to know if you'd be willing to explain? Again, thanks very much. Now I can do this in an hour rather than a week.

The Advanced filter requires a header row. In fact, if it doesn't find one, it usually brings up a message box telling you so. It might be taking your first row as your header in which case it's not including that entry in your results (which won't matter if you have a duplicate of it). Best to be safe and include the header row. Excel automatically recognizes it and ignores it.