[RESOLVED] Sorting two matrices

[Arithos]

Hello! I'm new to this forum, tho I have read many posts I have yet to post my own, so here it is!

In the following attachement I have a sorting issue i have tried different macros/formulas to sort. The "Start" sheet shows the raw data, and the "Finish" sheet shows the result I want.

The sorting process,

You can look at it as two big matrices divided into smaller ones:
"Big"
- Coloumns A:F (Ref:1)
- Coloumns G:J (Ref:2)
Divided into "smaller"
- Coloumns A2:F3
- Coloumns G2:J3 and so on..
and between the value "Total lines" in coloumn "A".

what I want is coloumns "J" and "D" to match eatchother, within the "Total lines" areas.

If there is more then one of the same observation in "J" or "D" I'd like there to be inserted a row, and the observation put there. If there is no match, it can just be listed below ref1 or ref2, respectively.

Does any of you know a simple way to sort this? Matrix array sorting with match? Alle suggestions are appreciated!

Sorting .zip

[FunkyDexter]

Moved to Office Development. You should get better replies here.

Also, I'd advise against attaching zips as mosty people won't download them (a. it's inconvenient and b. we can't be sure they're safe). You'll get a better response if you post your code instead.

Cheers
FD

[westconn1]

@ funky

Also, I'd advise against attaching zips

in this forum, you can not attach workbooks without zipping, better to post the code, though in this case he has no code at all, but often sample data is required to test

@ arithos
what have you tried so far?
there is no indication of this in your wokbook

[Arithos]

Hi again,

@westconn1: You are correct I could not attach xls, or xlsx here, and my exceeded the limit when I tried xml. Hence, a zip was the result.

And, my attempt thus far includes making the macro insert different
"=IF(AND(maaany criterias);"Command if criterias hold";"")"
and use a simple:

For x=1 to LR(last row in my code)

If "Command if criterias hold" then

My code for moving to different sheets, sorting via inserting new IF statements.

End if

Next x

And repeat....

This is time consuming, and contains an error = Me, and my ability to create good formulas.

and example of IF statement inserted into the "Start" sheet in my .zip

Sheets("Start").Select
LR = [counta(A:A)-1]
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-9]=0,RC[-8]=0,RC[-4]=0,RC[-2]=0),""Skip"","""")"
Selection.AutoFill Destination:=Range(Cells(y, 12), Cells(LR, 12))


then I loop this:

If Not Cells(y, 12) = "Skip" Then

Cells(y, 1).Select
Selection.Resize(Selection.Rows.Count, Selection.Columns.Count + 10).Select

FE = Selection.Copy

Sheets("Finish").Select
Cells(1, 1) = "x"
Cells(2, 1) = "xx"

Cells(1, 1).Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Start").Select
End If
Next y


And I perform this type of approach to create the end result, which does not always yield a perfect one. (Some values are pasted overeachother and "disappear" )


I have also tried different sorting approaches, but theese fails if there are too many observations not registered with either the left or right side of my DataSet.

I hope this gave some insight into what I have done thus far, I'm sure there is an easier way to do it, and hope someone might be able to enlighten me

[westconn1]

this is an unfinished code
please test, comprehensively, and comment back on any errors, wrong results and what else it should do

Dim s As Worksheet
Set s = Sheets("start")
Set f = Sheets("sheet1")
f.UsedRange.Clear   'start fresh
Set tr = s.Range("a2")
Do
    Set fnd = s.Range("a:a").Find("Total lines", tr, xlValues)
    If fnd Is Nothing Then Exit Do
    If fnd.Row < rw Then Exit Do
    Set grp = s.Range(fnd, tr.Offset(, 9))
    Set tr = fnd.Offset(1)
    nexrow = f.Cells(f.Rows.Count, 1).End(xlUp).Row + 1
    rw = grp.Row + grp.Rows.Count
    Set targ = f.Cells(nexrow, 1).Resize(grp.Rows.Count, 10)
    targ.Value = grp.Value
    For Each c In targ.Columns(4).Cells
        If Not IsEmpty(c) Then
            Set fnd = targ.Columns(10).Find(c)
            If Not fnd Is Nothing Then
                If Not fnd.Row = c.Row Then
                    c.Offset(, 3).Resize(, 4).Value = fnd.Offset(, -3).Resize(, 4).Value
                    fnd.EntireRow.Delete xlShiftUp
                End If
            End If
        End If
    Next
    DoEvents
Loop

i have not dimensioned most of the variables, so you should do so, any using the set keyword are worksheets or ranges, change sheetnames etc to suit your requirements

[FunkyDexter]

you can not attach workbooks without zipping, better to post the code, though in this case he has no code at all,

Ah. I assumed there was an existing macro in the workbook. I didn't open it to find out.

[Arithos]

@westconn1

Your code is annoyingly good
I am struggeling to understand all of it since its abit out of my reach as of yet (started w VBA two months ago) I can only see one "Error", and that is when there are two similar lines according to your coding like below:

FR0000121972 58 780, EUR EUR 56 977, 1 803, FR0000121972
FR0000121972 58 780, EUR EUR 1 803, 56 977, FR0000121972

It only returns the first one, and deletes the second one, its preferable that both are present, or that there is a notation behind (one coloumn to the right) it if possible.

Other then that.. No words, I understand I've just scratched the surface of whats possible in few lines...

Thx alot!

Edit: In essence you could just "leave them alone" since the rows with observations in coloumn "I" is already matched to the desired format.

[westconn1]

try changing to
If Not IsEmpty(c) Or c = c.Offset(, 6) Then

alternatively try
Set fnd = targ.Columns(10).Find(c, c.Offset(, 6))

[Arithos]

I've learned ALOT! Thank you for your help, as a finishing comment, can you recommend a book/site/ place other then this forum to educate myself in the more advanced stuff, like you just did

[westconn1]

can you recommend a book/site/ place other then this forum

not really, i learn most here answering for others, there are other forums that help with formulas and /or code, but this seems good for most general office application problems, code or formula
there are, i am sure, many books and references, but i have not had any on office application object models

i still have to test that i get the correct ranges returned, when using offset and resize etc

[Arithos]

@westconn1

Thank you, I'll keep an eye on this forum

Btw the code "If Not IsEmpty(c) Or c = c.Offset(, 6) Then"
did not work, but adding a new if statement like so:
If Not c.Offset(, 6) Then
End if

and it works

i still have to test that i get the correct ranges returned, when using offset and resize etc

Is there an easy way to test this?

[Arithos]

@westconn1

Thank you, I'll keep an eye on this forum

Btw the code "If Not IsEmpty(c) Or c = c.Offset(, 6) Then"
did not work, but adding a new if statement like so:
If Not c.Offset(, 6) Then
End if

and it works

i still have to test that i get the correct ranges returned, when using offset and resize etc

Is there an easy way to test this?

[Arithos]

Delete

[westconn1]

Is there an easy way to test this?

yes in the immediate window at a break in the code
like
?fnd.Offset(, -3).Resize(, 4).address

[Arithos]

aaah, nice,

tank you!