Excel sorting without disturbing the main file

Hi,

I have a excel sheet with all data and another sheet which has same contents or some differences.
What i want now is to compare 2 sheets and sort the 2nd file as the first sheet.

egards
Sharath
LVL 11
bsharathAsked:
Who is Participating?
 
Hitesh ManglaniCommented:
try this macro
Sub asort()
s2row=1
For i=1 to Sheet1.UsedRange.Columns.Count
   For j=1 to Sheet1.UsedRange.Rows.Count
      For k=1 to Sheet2.UsedRange.Rows.Count
         if UCase(Sheet2.Cells(k,i)) = UCase(Sheet1.Cells(j,i)) then
               temp=Sheet2.Cells(s2row,i)
               Sheet2.Cells(s2row,i) = Sheet2.Cells(k,i)
               Sheet2.Cells(k,i) = temp
               s2row=s2row+1
        End if
     Next
   Next
Next
                 
End Sub
0
 
JonybrvCommented:
If you want to check if the column is missing or new in the second file, you can do that using vlookup function. vlookup can be used to check if the data in second file is available in the first file or vice versa.
If your first file is sorted, you can pull up the data from second and first sheet to a new sheet keeping file 1 sort order.


0
 
JonybrvCommented:

Oh missed to mention, if you are trying to avoid manual work. You might need to come up with a macro.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
bsharathAuthor Commented:
Any help with a Macro
0
 
BadotzCommented:
Go to Tools->Macro->Create Macro and accept the defaults. Now whatever you do within Excel will be recorded until you stop recording. Handy for figuring out how things work.
0
 
Hitesh ManglaniCommented:
>What i want now is to compare 2 sheets and sort the 2nd file as the first sheet.

Can you elaborate this point sharath
0
 
bsharathAuthor Commented:
Say i have file A as
Sharath
Ramesh
Suresh
Kiram
And second file as
Ramesh
Kiram
So what i want is that the second file will also be changed as the first file sorting
Sharath
Ramesh
Suresh
Kiram
Suresh
0
 
Hitesh ManglaniCommented:
so the order of the names in the second file should be same as the order of sorting in the first file right.
If thats correct then in the above example you gave, the second file will have only two names Ramesh and Kiran.
0
 
bsharathAuthor Commented:
Sorry actuall the the second file will have
Kiran
Ramesh
Sharath
Suresh
0
 
Hitesh ManglaniCommented:
i still really dont get the objective.sorry buddy
0
 
bsharathAuthor Commented:
I have a file A
Which has
Sharath
Reddy
Ramesh in this order
and file B in this order
Ramesh
Sharath
Reddy
Now i want a way for the macro to sort the second file as it is in the first file.

0
 
Hitesh ManglaniCommented:
try this macro
Sub asort()
s2row=1
For i=1 to Sheet1.UsedRange.Columns.Count
   For j=1 to Sheet1.UsedRange.Columns.Count
      For k=1 to Sheet2.UsedRange.Rows.Count
         if Sheet2.Cells(k,i) = Sheet1.Cells(j,i) then
               temp=Sheet2.Cells(s2row,i)
               Sheet2.Cells(s2row,i) = Sheet2.Cells(k,i)
               Sheet2.Cells(k,i) = temp
        End if
     Next
   Next
Next
                 
End Sub


0
 
bsharathAuthor Commented:
I think this is case sensitive.
This is not so acurate.Can you please check its sorting 1st 2 lines. But when there are many rows its not sorting
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.