Column ordering function/process

[ilnovi]

Hi,

Thank you for giving me the possibility of posting here my issue. I am a Surgeon, and I am doing a research project. My job is to perform a chart review and record patient's variables. Thus the structure of my excel files is pretty simple: patient identifier=rows and variables=columns.

My issue is the following: I have some patients(=rows) that are missing in one file but are present in another file. Thus the goal is to bring some rows (i.e. patients) from one file to another. The problem is that since the variables (=columns) are slightly different (for number and quality) from file to file, I cannot copy and paste. The fastest thing that I came up is just manually ordering the columns on one file so that it has the same order of the other file (at the cost of adding blank columns). Nevertheless, this is a very lengthy and time-consuming process since I have almost 300 columns in each file.

Long story short: is there a function that could help me ordering the columns of one file so that they correspond to that of another file?
If I could achieve this, then I could perform a simple copy paste of the rows I am interested in from file to file.


Any other idea is appreciated, but please try to avoid a VBA solution since I do not know anything about it (at the first issue I would be screwed).


Thank you!


Cheers

 

[kbrummert]

1. Copy just the column header row from each. Paste them in separate columns on a blank sheet, a few columns apart from each other. Paste as transpose values, so that the column names of each respective sheet are in rows.
2. Do a vloolup on each respective header-name-column against the other to see which are missing in each. Whichever is missing in each, copy and paste-transpose them (so now they are in column order again) in the top row of the sheet to the right of the actual rows, so now you have headers for blank columns in each. Do not worry that they are out of order for this step, so long as you have the same column names at the top of both sheets (count columns as a check).
3. Go into each and highlight the entire range of rows and columns, including the blank ones. Do a sort, but not by rows. Do it left to right as explained in the link below, using row 1 as the sort criteria.
4. In the end, you should have the same columns in each sheet sorted from left to right the same way--some blank in one, some blank in the other. From there, you can paste them both in another sheet and you will have a consolidated list with the columns aligned.

All the steps above should be Excel 101 or Excel 102, so they will be easy to look up if you are unfamiliar.

http://excelsemipro.com/2011/02/sorting-columns-left-to-right-in-excel/

 

[ilnovi]

1. Copy just the column header row from each. Paste them in separate columns on a blank sheet, a few columns apart from each other. Paste as transpose values, so that the column names of each respective sheet are in rows.
2. Do a vloolup on each respective header-name-column against the other to see which are missing in each. Whichever is missing in each, copy and paste-transpose them (so now they are in column order again) in the top row of the sheet to the right of the actual rows, so now you have headers for blank columns in each. Do not worry that they are out of order for this step, so long as you have the same column names at the top of both sheets (count columns as a check).
3. Go into each and highlight the entire range of rows and columns, including the blank ones. Do a sort, but not by rows. Do it left to right as explained in the link below, using row 1 as the sort criteria.
4. In the end, you should have the same columns in each sheet sorted from left to right the same way--some blank in one, some blank in the other. From there, you can paste them both in another sheet and you will have a consolidated list with the columns aligned.

All the steps above should be Excel 101 or Excel 102, so they will be easy to look up if you are unfamiliar.

http://excelsemipro.com/2011/02/sorting-columns-left-to-right-in-excel/

you are right kbrummert! This is a great idea and does the trick.

However, the situation is much more complicated than what I described since I already have quite a bit of data for those missing patients (their row is actually already present in the first file but with just some variables recorded). Thus I cannot simply copy paste from one file to the other.

I think that this detail makes things a little to challenging.

 

[NoSparks]

A few questions come to mind when reading your request

. is your intent to 'update' all patients or only one at a time?
. how many files are you talking about?
. how do you know which files you want to deal with?
. are said files open or do you need to go looking for them?
. what column are patient identifiers in?
. what row are column headers in?
. do column headers match, just not in order?
. and I'm sure there's other things needing to be known in order to assist.

How about mocking up and sharing a couple of (small) sample workbooks indicative of what you're working with so that we can test possible solutions.

Regarding the VBA comment... "at the first issue I would be screwed" ...wouldn't you just be back to the position you're in now?

Please see 13 here and provide links to your cross postings, thanks.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-general/1215473-column-ordering-function-process.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[ilnovi]

Cross posted https://www.excelforum.com/excel-general/1215473-column-ordering-function-process.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Hi this is the link to the cross post.
https://www.excelforum.com/excel-general/1215473-column-ordering-function-process.html#post4820505

At the moment I am busy for an interview job but I appreciate all the help from the users and will get back to you ASAP.
Please let me know if I should do anything else to comply with the rules.

thank you