Formula to sort columns in a spreadsheet accordingly

I have a spreadsheet that I receive each week from the HR department that contains data about new hire employees. I have other spreadsheets that retrieve data from this new hire worksheet each week. The problem is that the person sending the spreadsheet doesn't always have the columns sorted in the same order so my formulas are all out of whack because the data isn't in the same rows. How can I setup a formula or macro that arranges all of the columns in the order that I need? I have attached an example. Thanks for your help!

This Macro will do that.

sheetname must be incorrect

Please Login or Register  to view this content.

Thanks for your help Mehmetcik, this works great but suppose the columns are not always sorted in any specific order as in my 'incorrect sheet' example. Is it possible to modify this macro so that it sorts the columns to match the 'correct sheet' regardless of what order they are in? So for example, the macro should sort the columns the same regardless if the Name column is in A1, B1, C1 OR D1? Thanks again.

Originally Posted by FDibbins

Ty this approach using regular formulas (no VBA required.

On the sheet where you want the answers to be...
1. copy this down in the EE ID column (B?)...
=IFERROR(SMALL(Incorrect!D:D,ROW(A1)),"")
2. In the other columns, use this, copied down (for all the other columns
=IF($B2="","",INDEX(Incorrect!$A:$D,MATCH($B2,Incorrect!$D:$D,0),MATCH(A$1,Incorrect!$A$1:$D$1,0)))

FDibbins, I would like to avoid VBA but I can't seem to get your formula to work. I'm sure I am doing something wrong. Here is what I get when I apply the formula:

0 41640 0 0
0 41641 0 0
41642

What is you expected results? describe it at your sample workbook


thanks

LOL

Thats exactly what I did originally.

But that means your original file name must be fixed to.

I will ammend it back to that version.

Try This:-

Please Login or Register  to view this content.

Try This:-

Please Login or Register  to view this content.

Mehmetcik, this works perfectly! Thanks so much.