Sort Based On Two Independent Cells

salimthaj · May 21st 2007

Dear All,

Need your help in fixing this.

In the attached spread sheet, i have two set of data, for two different months.Columns A to D have the value for the first month and columns F to I have the values for the second month. In the first set of Values i have the dollar value in the column "D" and in the second set of values i have the dollar value in column "F". I want a macro, to compare these two cells and arrange the data accordingly. If both the values are the same, then both the lines corresponding to the values can remain in the same row. If one value is greater that other, then the set of values which have the lower dollar value should come to the next row. I have attachec the sample data. The lines highlighted in green is the raw data and the one in yellow is the finished data.

Is this workable???

Can Somebody Help....

Thank You

Karebac · May 21st 2007

Re: Sort Based On Two Independend Cells

I am short on time, because I am at this annoying place called "work",
but glancing at your post, I have the intuitive reaction that one might "build" a sort column out of any number of programatically concatenated values with VBA, and then sort the entire range on that contrived sort field. I used to do such in other programming languages like PICK basic, and RPGII.

Just thought I would throw out that idea in support of the notion that any manner of sort is do-able, if you are willing to build a sort column programatically.

face2face · May 21st 2007

Re: Sort Based On Two Independend Cells

Hi salimthaj,
I´m loocking at your attached sheet and I was assuming that the the yellow part is how you want your macro generated your data. However is it normal that in row 12 you assume that the data for PEMEX GAS Y PETROQUIMICA BASICA is the same for column D and F. Indeed PETROBRAS AMERICA INCis also equal to 263 967. I assume it is an error. Just want to be sure.

Cheers.
Frederik

salimthaj · May 21st 2007

Re: Sort Based On Two Independend Cells

Hi face2face,

If you compare the cells D4 and F4, F4 is greater than D4, that is the reason why the corresponding values to D4 are brought a step down.Now, we will compare D5 with F5, since the values are the same. No change here. Hope i answered your question.

face2face · May 21st 2007

Re: Sort Based On Two Independend Cells

Hi,
Well in the attached files, D5 and F5 are not equal, therefore I was woundering.
D5=198 860,00 F5= $263 967,00 .

Let me know
Frederik

salimthaj · May 21st 2007

Re: Sort Based On Two Independent Cells

Hi Fredrick,
In the attached example, the area highlighted in green is the raw data and the one in yellow is the finished data. In raw data, first compare the cells D3 & F3, Since they are equal, we leave them as it is, Now compare the cells D4 & F4, Since D4 is less than F4, we pull the data corresponding to D4 by a step down,which is shown in row 12.Now D12 & F12 becomes equal and so we go to the next row and henceforth....

face2face · May 22nd 2007

Re: Sort Based On Two Independent Cells

Hi,
I´m not sure this is exactly what you want but I think it is pretty close.
Have a look and let us know.

Code

Sub Copy_Highlight()
 Dim FirstBlank As Range
For i = 3 To 6
 
  Set FirstBlank = Cells(Rows.Count, 6).End(xlUp).Offset(1, 0)
    Range(Cells(i, 1), Cells(i, 10)).Copy
    FirstBlank.Offset(0, -5).Select
Selection.PasteSpecial
If FirstBlank = FirstBlank.Offset(0, -2) Then
Else
Range(FirstBlank.Offset(0, -5), FirstBlank.Offset(0, 4)).Select
    Selection.Copy
FirstBlank.Offset(0, -5).Select
    Selection.Insert Shift:=xlDown
Range(FirstBlank.Offset(-1, -5), FirstBlank.Offset(-1, -2)).ClearContents
FirstBlank = FirstBlank.Offset(0, -2)
End If
Next i
End Sub

Display More

Hope that helps.
Cheers
Frederik

salimthaj · May 22nd 2007

Re: Sort Based On Two Independent Cells

Dear face2face,

This is fine, but need a small favour too... If you see the way the macro works, whenever a value in "F" is chosen as the highest, the corresponding "D" values move one step down and the values corresponding to "D" in that particular row are empty. but the same doesn't happen the other way around. When D has the higher value the corresponding values of F are not empty. Could you fix this please...

Thanks a ton for your help...

face2face · May 22nd 2007

Re: Sort Based On Two Independent Cells

Hi,
I will have a look at it tomorrow. It´s getting late here and I´m sure I get it right now. I can suggest you to try som If condition for D > F. If you don´t manage. Give an example of where it is occuring in your file it would help me understand.
Frederik

salimthaj · May 22nd 2007

Re: Sort Based On Two Independent Cells

Hi face2face,

If you see the attached sample2, the area highlighted in green, it is fine. But the ones highlighted in Red shouldn't have been there as per the criteria

face2face · May 24th 2007

Re: Sort Based On Two Independent Cells

Hi,
I haven't tested but this should be ok

Code

Sub Copy_Highlight()
 Dim FirstBlank As Range
For i = 3 To 6
 
  Set FirstBlank = Cells(Rows.Count, 6).End(xlUp).Offset(1, 0)
    Range(Cells(i, 1), Cells(i, 10)).Copy
    FirstBlank.Offset(0, -5).Select
Selection.PasteSpecial
If FirstBlank = FirstBlank.Offset(0, -2) Then
Else
Range(FirstBlank.Offset(0, -5), FirstBlank.Offset(0, 4)).Select
    Selection.Copy
FirstBlank.Offset(0, -5).Select
    Selection.Insert Shift:=xlDown
Range(FirstBlank.Offset(-1, -5), FirstBlank.Offset(-1, -2)).ClearContents
Range(FirstBlank.Offset(0, 1), FirstBlank.Offset(0, 4)).ClearContents
FirstBlank = FirstBlank.Offset(0, -2)
End If
Next i
End Sub

Display More

salimthaj · May 24th 2007

Re: Sort Based On Two Independent Cells

Thanks a ton face2face...

But, a small problem, i am not able to find out why. The Macro brings up an error when i put this data set. Could you advise on the same please. Sorry for taking up your time.

face2face · May 24th 2007

Re: Sort Based On Two Independent Cells

Hi,
The error comes from the fact that you change 6 to 300. 6 refer to the column. The code define the firrst blank cell by for the cell after the last non empty cell in the 6th column (F). It should stay like this. I forgot to mention that for exeample you should copy your labels where you want to sort your list. So I've paste it a couple of line bellow your list.
I set up the loop to do until 386. But if you want less just change this 386. Don't touch to this 6. Execute the code it works. I just forgot to set the display update to false to execute the code faster. If you want just enter

Code

Application. ScreenUpdating = False

at the begining of the sub and

Code

Application. ScreenUpdating = True

just befor the End Sub

Hope this is ok.

salimthaj · May 24th 2007

Re: Sort Based On Two Independent Cells

Hi face2face,

I didn't get the updated file. could you attach that please.

face2face · May 24th 2007

Re: Sort Based On Two Independent Cells

My Mistake, Sorry

Sort Based On Two Independent Cells

Participate now!

Share