Unscrambling a randomised sheet
I had an accident with an Excel worksheet I was working on.
The rows in one sheet should have been randomised - I generated a column of RAND() then copied and pasted the values, then sorted by lowest to highest - this randomise worked lovely!
It worked lovely except for the fact it missed 1 column. I carried on unaware that the rows of data were now 'broken'. To illustrate (column E is the rand() column):
Before:
Column A Column B Column C Column D Column E
1 <blank> important data 1 more important data 1 16546
2 <blank> important data 2 more important data 2 213544
3 <blank> important data 3 more important data 3 1231
After:
Column A Column B Column C Column D Column E
1 <blank> important data 3 more important data 3 1231
2 <blank> important data 1 more important data 1 16546
3 <blank> important data 2 more important data 2 213544
As you can see the data for row 3 now has row 2's data in it.
This file was then split into 2 files and used for its purpose, however due to analysis I need to perform on the data I need it to match up the data to its correct row.
So I have 2 files (that came from 1) that were randomly mixed before they were split.
I also have the 1 file before it was randomly mixed and split.
I need to match the data in columns C and D to the rightful owner in the respective row.
The only column untouched in both files is 'A' - so thinking logically I can use the untouched file to check where the data should go to.
Anyone have any ideas where to start?
I have tried using MATCH to indicate which row the incorrect entry could be found but that doesn't help when cells can have duplicate data in them.
I apologise if I haven't explained the issue properly but any help would be greatly appreciated!
The rows in one sheet should have been randomised - I generated a column of RAND() then copied and pasted the values, then sorted by lowest to highest - this randomise worked lovely!
It worked lovely except for the fact it missed 1 column. I carried on unaware that the rows of data were now 'broken'. To illustrate (column E is the rand() column):
Before:
Column A Column B Column C Column D Column E
1 <blank> important data 1 more important data 1 16546
2 <blank> important data 2 more important data 2 213544
3 <blank> important data 3 more important data 3 1231
After:
Column A Column B Column C Column D Column E
1 <blank> important data 3 more important data 3 1231
2 <blank> important data 1 more important data 1 16546
3 <blank> important data 2 more important data 2 213544
As you can see the data for row 3 now has row 2's data in it.
This file was then split into 2 files and used for its purpose, however due to analysis I need to perform on the data I need it to match up the data to its correct row.
So I have 2 files (that came from 1) that were randomly mixed before they were split.
I also have the 1 file before it was randomly mixed and split.
I need to match the data in columns C and D to the rightful owner in the respective row.
The only column untouched in both files is 'A' - so thinking logically I can use the untouched file to check where the data should go to.
Anyone have any ideas where to start?
I have tried using MATCH to indicate which row the incorrect entry could be found but that doesn't help when cells can have duplicate data in them.
I apologise if I haven't explained the issue properly but any help would be greatly appreciated!
Elton Pascua
I suggest that you create a column on each file, concatenate as many fields as possible to prevent duplicated value and then use those columns for matching.
ASKER
Good shout, I've not got an accurate location...how's easiest to combine the data now?
I'm sorry I don't understand your last post.
Are you asking how to combine columns? If so, do something like:
Are you asking how to combine columns? If so, do something like:
=concatenate(A1,B1,C1,D1)
If 2 rows of the scrambled and are identical, then I would use a countif on your concatenated field and sort by that. This will group all the rows which vlookup/match won't work correctly. Then manually assign those values. (Do the same thing to your original data to group it too)
The rest, vookup/match will work on.
Catentenate as techfanatic has said, or you may need to delimit the values, eg
The rest, vookup/match will work on.
Catentenate as techfanatic has said, or you may need to delimit the values, eg
=A1 & ";" & B1 & ";" & C1ASKER
Sorry for not being clearer, that last post had a typo ¬_¬ It was meant to read that "I have got an accurate location"...not "I have not got an accurate location".
I've combined the data and separated with commas - will be handy if saving to a text csv file to separate to cells again - the next question is really a 'How do I use Vlookup effectively?'.
I've combined the data and separated with commas - will be handy if saving to a text csv file to separate to cells again - the next question is really a 'How do I use Vlookup effectively?'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help! Managed to get my issue resolved.