Arranging data that is in columns in a different dynamic way

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

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.

Why don't you use in Z47:

PHP Code:

---

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


---

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).

Quote:

---

(although that is throwing up an error).

---

So the picture doesn't exist ?

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

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

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.

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

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.

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

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

Code:

---

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.

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

You are.

Code:

---

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

---

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

You have my permission to adapt the code.

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.

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

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.

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

---

Thanks Okami - that is a pretty neat solution!

Will work on it.

Thank you very much once again.

Hi Okami

I am making some progress however have 2 issues:

1. If there are no candidates with codes NN or SS (for e.g.), then the code is not liking that and I am getting an error. It's looking for atleast one candidate with a combination of NN, SS, NS or SN but it could be the case that no candidates exist in one of the groups (at a given point in the year as the list/codes is always changing). Otherwise it works fine.

2. Is it possible to 'fix' the array regions in columns B:C and F:G because if there is any data/text in column D for e.g. the code is not liking that either and I get an error. Also if there is any data in Cell Y45 (once cell above where it says Group 1) the code does not like that either. It is possible I may have data in these cells and didn't realise it would be an issue.


Also if I wanted my lists enclosed in a border would I add something like ...

Code:

---

Cells.Borders().LineStyle = xlContinuous

---

Thanks you very much once again

Hi branston!
Fixed 1 and 2 and border's requirements.
About position I'll take time to look at it.
But I don't think it's a good way to do it. There will still be problems in the future.

Code:

---

Sub Okami()
Dim arrOri, arrRst, d As Object, i&, c, cnt&, rng As Range, r&, rTop&, rBtm&
Set d = CreateObject("scripting.dictionary")
'--------Search------------------------------------------
rTop = Columns("b").Find("GROUP 2").Row + 1
rBtm = Cells(rTop, 2).End(4).Row
arrOri = Range(Cells(rTop, 2), Cells(rBtm, 3))
ReDim arrRst(1 To UBound(arrOri), 2)
For i = 1 To UBound(arrOri)
  If arrOri(i, 2) = "N" Then d(arrOri(i, 1)) = ""
Next i
rTop = Columns("f").Find("GROUP 2").Row + 1
rBtm = Cells(rTop, 6).End(4).Row
arrOri = Range(Cells(rTop, 6), Cells(rBtm, 7))
For i = 1 To UBound(arrOri)
  If arrOri(i, 2) = "N" Then
    If d.exists(arrOri(i, 1)) Then
      cnt = cnt + 1
      arrRst(cnt, 0) = cnt
      arrRst(cnt, 1) = arrOri(i, 1)
      Set rng = Sheets("DATA").Columns("b").Find(arrOri(i, 1), 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
If cnt = 0 Then MsgBox "No candidates exist in this group.": Exit Sub
'--------Search------------------------------------------


'--------Output------------------------------------------
rTop = Columns("y").Find("group 1").Row
rBtm = Columns("y").Find("group 3").Row
r = rBtm - rTop - 8
Range("x" & rTop + 1 & ":z" & Cells(rBtm - 2, "y").End(3).Row) = ""
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
Set rng = Cells(Columns("y").Find("group 1").Row + 1, "x").Resize(cnt, 3)
rng = arrRst
rng.Columns(1).Interior.Color = vbYellow
rng.Borders().LineStyle = xlContinuous
'--------Output------------------------------------------
End Sub

---

Thanks Okami

Not sure why I am getting an error here

Code:

---

Columns("y").Find("group 1").CurrentRegion.Offset(1) = ""

---

"Object variable or With block variable not set" ... everything looks ok????????

I'm sorry I forgot to delete that code line.

Sorry still getting same error on last few lines of code and don't know why

Code:

---

Set rng = Cells(Columns("y").Find("group 1").Row + 1, "x").Resize(cnt, 3)

---

Code:

---

Columns("y").Find("group 1").Row

---

There is only one possible mistake in this sentence.
Verify if "group 1" of column Y has been deleted. Or they become capital letters. Not sure.

Have had a good look at it but cannot see why getting the error. I think it's the way it's searching for Group1??

I've attached an example sheet. Could you please take a look ?

Thank you

Hi branston!
Yeah, I didn't think the number of rows would go wrong without data.
I have revised the code. Please try again.
only one line:

Code:

---

Range("x" & rTop + 1 & ":z" & Application.Max(Cells(rBtm - 2, "y").End(3).Row, rTop + 1)) = ""

---

BTW: I don't know why I can't upload attachments.

--Okami

Thanks Okami - that seems to have fixed that bit.

I seem to be having an issue with the groups3 and 4 moving but will try to fix it and come back if I need help.

Thanks again for all your help.

Okami if you have a look at the attached file you will see that when the Group 1 list is very long, Group 3 AND Group 4 auto-adjust. However when Group 2 is very long (and group 1 may be short ... you can change the codes in columns c and g to N and S etc.), only Group 4 auto-adjusts and Group 3 stays where it is. I don't understand as using the same code really for each group?

1)Group1 and group2 should have the same extension.

Code:

---

If cnt > r Then Cells(Columns("ad").Find("group 4").Row - 7, "x").Resize(cnt - r + 1, 8).Insert Shift:=xlDown

---

2）If there is nothing under group3 and group4, you can simplify the code.

Thanks Okami. That works 99% so will do for now.

For error checking purposes I need to do a count of candidates in all 4 lists. Obviously the lists change in length. Is this easy to do using an excel formula e.g. (countA(lookup 'group 1' + last row)) or would I have to use VBA because of the "lastrow" search?

Thanks again for all your help!