Arranging data that is in columns in a different dynamic way

[branston]

Hi

I have some candidate names and their photos arranged in 4 'group columns'. I want to arrange the candidate names/photos in a 2x2 arrangement where groups 3 and 4 are 'underneath' groups 1 and 2.

I am having 2 problems.

Firstly due to the way the code is written if for e.g. Group3 is placed underneath group1, group3 will be overwritten when viewing/running macro for Group1 candidate pictures. Second - Each group list is dynamically generated (so its length can change). I therefore need the 2x2 table format to adjust according to the size of the list and Group3 and Group4 lists to start a few rows after the 'lastrow' of Groups 1 and Group 2.

I appreciate that it is easier to do this using the 4 group column method but that is not how I need the data presenting.

I have attached my file (irrelevant data stripped out) and would greatly appreciate it if anyone can help.

Thanks in advance

[branston]

Just to add and to be clearer ... that I left the names from dynamically generated list for each group on the attached sheet without actually including that macro/code since I thought it was be easier for people to understand the actual problem which is the 2x2 array arrangement. If that piece is needed then please let me know.

[snb]

Why don't you use in Z47:

=INDEX(DATA!$C$2:$C$51;MATCH(Y47;DATA!$B$2:$B$51;0))&".jpg" 

[branston]

Thanks snb for the reply - (although that is throwing up an error).

Not sure how that resolves the issue of Groups 3 and 4 appearing 'under' Groups 1 and 2??

I am open to ideas, maybe there is a better way of doing this than the one I am pursuing (thanks to TG for the help originally).

[snb]

(although that is throwing up an error).

So the picture doesn't exist ?

[branston]

No sorry it's a 'There's a problem with this formula' error

[branston]

Hi

I have attached my file again including the code for how the Group1 list (as an example) is generated. Since the codes in column 'C' and column 'G' can change, I am hoping you can see the issue I am having in placing Groups 3 and 4 under groups 1 and 2 and understand the problem better?

Many thanks in advance for any pointers/help

[Kenneth Hobs]

I did not respond to this earlier because I don't understand the goal. If you have code that is already filing the 4 groups in 4 columns, why can it not be changed to do 2 columns?

If you want to use what works now and move the group 3 under group 1 and group 4 under group 2, that is easily done by a manual cut/paste or by a macro that does it. Is that all that you need?

You say add extra blank rows between the two stacked groups. That is easily done too.

As for being dynamic. I am not sure what that means. I have helped with other threads where a worksheet change event deleted the picture in another column and added another picture based on the change.

[branston]

Hi Ken

Thanks for replying.

By dynamic I meant that the list can change length depending on the criteria in columns C and O.

Yes that's all I need.

The problem I have is that if I cut and paste group 3 under group 1 and group 4 under group 2 ... when the macro for group one is run it overwrites everything in columns Y and Z i.e. overwrites group 3. I need it to stop after the last candidate in group 1 and when the macro for group 3 is run... for that to return values in a few rows below wherever group 1 stopped. Same for group 4 and group 2. (At the moment the values where group 3 and 4 start are 'hard coded' and obviously these need to be 'relative' according to where the previous groups finish/stop.)

I hope that makes sense. I thought it was a simple enough problem but couldn't sort it so left the data in four group columns for you to see.

Also I have 8 macros running to sort these 4 groups out. 4 for the group lists and 4 for the associated pics. I'm not sure that is the most efflcient way of doing it?

Thanks once again

[Kenneth Hobs]

I have some work deadlines so I may not get to look at this until the weekend.

Kind of clunky I guess but why not just delete/clear everything to do it all? Another way to do it might be to do 5 worksheets. 4 would be for the groups. 5th would be the clear all add all 4 in order to suit.

I think the 4 groups have there own macros which are nearly the same. In that case, passing the variables to a common routine will make upkeep much easier. e.g. Pass the worksheet object to the main sub. It would then do its thing for that sheet.

[branston]

I did think about the fact that all macros do nearly the same thing so passing variables to a common routine just makes sense.

Not entirely clear on what you mean to ‘clear everything ...’ The 5 worksheets sounds interesting but not sure exactly how that would be executed

i guess I'm kind of surprised that moving to groups under another two groups would be re-hashing everything.

Anyway I will keep working on it and look forward to any help you can provide

As ever thanks for your help

[snb]

Use builtin Options in Excel.
In this case 'advancedfilter'.

Sub M_snb()
  With Sheet1 
    .Cells(50, 2) = .Cells(3, 2).Value
    .Cells(1, 15).Resize(2) = Application.Transpose(Array(.Cells(3, 3), "N"))
    .Cells(4, 1).CurrentRegion.AdvancedFilter 2, .Cells(1, 15).CurrentRegion, .Cells(50, 2)
    .Cells(1, 15).Resize(2).clearcontents
  End With
End Sub

Put this macro in the macromodule of Sheet1, not in a separate Macromodule.

[branston]

Thanks snb.

Pardon my ignorance but 2 things.

1. My original criteria was looking for 'N' in BOTH columns C and G not just column C.
2. Still don't understand how this is making the last row of group 1 relative. The code, to me, still seems to hard code their position? The lists can change size so how to make sure group 3 which is to be placed under group 1 starts a few rows AFTER where group 1 ends (which like I say can vary in size.)

I'm not sure using a filter is the way forward? Please correct me if I am wrong !

Thanks again

[snb]

You are.

Sub M_snb()
  With Sheet1
    .Cells(1, 15).Resize(2) = Application.Transpose(Array(.Cells(3, 3), "N"))
    .Cells(Rows.Count, 1).End(xlUp).Offset(10, 1) = .Cells(3, 2)
    For j = 0 To 1
        .Cells(4, 1).Offset(, 4 * j).CurrentRegion.AdvancedFilter 2, .Cells(1, 15).CurrentRegion, .Cells(Rows.Count, 2).End(xlUp)
        If j = 0 Then .Cells(Rows.Count, 2).End(xlUp).Offset(1) = .Cells(3, 2)
    Next
    .Cells(1, 15).Resize(2).ClearContents
  End With
End Sub

[branston]

Thanks snb.

If you look at the module 2 macro you'll see what I mean by code 'N' in BOTH columns C and G ie. the overlapping candidates, so should only be one list (starting in cell X47) and should be numbered ie. 1,2,3,4 etc.

I take it that I need to use this advance filter with all 4 of my lists? and the group 3 list will auto-start a few rows after where group 1 ended?

Thanks once again

[snb]

You have my permission to adapt the code.

[branston]

Thanks snb I'll give it a shot.

Although I think you've misunderstood my original question. It was the group 3 list in column AI that needed to be underneath column Y list (with a few rows in between).

I've added my sheet again with additional code so you can see what I mean but in anycase thanks for all your help.

[branston]

Hi

Have tried to amend the code but still having issues with group 1 overlapping group 3 and group 2 overlapping group4. Cannot get the advancefilter to place the values in the correct cells as I am new to VBA and struggling to work around snb's code.

Could someone look at my amended file to see what I mean?

Thanks

[大灰狼1976]

Hi branston!
In order to test the situation when there is too much data, you can change some of "S" to "N".
Since the attachment cannot be uploaded due to network problems, please refer to the following code.

Sub Okami()
Dim arrOri, arrRst, d As Object, i&, c, cnt&, rng As Range, r&
Set d = CreateObject("scripting.dictionary")
'--------Search------------------------------------------
arrOri = Columns("b").Find("GROUP 2").CurrentRegion
ReDim arrRst(1 To UBound(arrOri), 2)
For i = 2 To UBound(arrOri)
  If arrOri(i, 3) = "N" Then d(arrOri(i, 2)) = ""
Next i
arrOri = Columns("f").Find("GROUP 2").CurrentRegion
For i = 2 To UBound(arrOri)
  If arrOri(i, 3) = "N" Then
    If d.exists(arrOri(i, 2)) Then
       cnt = cnt + 1
       arrRst(cnt, 0) = cnt
       arrRst(cnt, 1) = arrOri(i, 2)
       Set rng = Sheets("DATA").Columns("b").Find(arrOri(i, 2), lookat:=xlWhole)
       If Not rng Is Nothing Then arrRst(cnt, 2) = rng.Offset(, 1) & ".jpg" Else arrRst(cnt, 2) = "xxxxx.jpg"
     End If
   End If
Next i
'--------Search------------------------------------------


'--------Output------------------------------------------
r = Columns("y").Find("group 3").Row - Columns("y").Find("group 1").Row - 8
Columns("y").Find("group 1").CurrentRegion.Offset(1) = ""
If cnt > r Then Cells(Columns("y").Find("group 3").Row - 7, "x").Resize(cnt - r + 1, 8).Insert Shift:=xlDown
Cells(Columns("y").Find("group 1").Row + 1, "x").Resize(cnt, 3) = arrRst
'--------Output------------------------------------------
End Sub

[branston]

Thanks Okami - that is a pretty neat solution!

Will work on it.

Thank you very much once again.