Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

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!
0
purpleoak
Asked:
purpleoak
  • 3
  • 2
  • 2
1 Solution
 
Elton PascuaCommented:
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.
0
 
purpleoakAuthor Commented:
Good shout, I've not got an accurate location...how's easiest to combine the data now?
0
 
Elton PascuaCommented:
I'm sorry I don't understand your last post.

Are you asking how to combine columns? If so, do something like:
=concatenate(A1,B1,C1,D1)

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
RyanProject Engineer, ElectricalCommented:
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
=A1 & ";" & B1 & ";" & C1

Open in new window

0
 
purpleoakAuthor Commented:
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?'.
0
 
RyanProject Engineer, ElectricalCommented:
concatenate both tables (broken one and original) the same way.
Make sure your concatenated value for the original is on the far left.

Then
=vlookup(<concat field in broken take>,<range of broken table with concat far left including your missing col>, <index of the missing col in that range>,false)
0
 
purpleoakAuthor Commented:
Thanks for the help! Managed to get my issue resolved.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now