If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Need to edit code to work with this spreadsheet please.

Thread starter SOLTEC
Start date Apr 19, 2015

SOLTEC

Board Regular

Apr 19, 2015

#1

Sub ConcatImageGroups()
Dim LastRow As Long, Ar As Range
LastRow = Cells(Rows.Count, "M").End(xlUp).Row
Range("O2:O" & LastRow) = Evaluate("IF(M2:M" & LastRow & "=1,1,"""")")
For Each Ar In Range("O2:O" & LastRow).SpecialCells(xlBlanks).Areas
Ar(1).Offset(-1).Resize(Ar.Count + 1) = Join(Application.Transpose(Ar(1). _
Offset(-1, 1).Resize(Ar.Count + 1)), ";")
Next
End Sub

Excel 2010

A

B

C

1

ID

Images

Combination Images

2

4658

1000.jpg

1000.jpg;1001.jpg;1002.jpg;1003.jpg;1004.jpg;1005.jpg;

3

4658

1001.jpg

1000.jpg;1001.jpg;1002.jpg;1003.jpg;1004.jpg;1005.jpg;

4

4658

1002.jpg

1000.jpg;1001.jpg;1002.jpg;1003.jpg;1004.jpg;1005.jpg;

5

4658

1004.jpg

1000.jpg;1001.jpg;1002.jpg;1003.jpg;1004.jpg;1005.jpg;

6

4658

1005.jpg

1000.jpg;1001.jpg;1002.jpg;1003.jpg;1004.jpg;1005.jpg;

7

4703

1024.jpg

1024.jpg;1025.jpg;1026.jpg;

8

4703

1025.jpg

1024.jpg;1025.jpg;1026.jpg;

9

4703

1026.jpg

1024.jpg;1025.jpg;1026.jpg;

10

4913

1031.jpg

11

4913

1032.jpg

12

4939

1035.jpg

13

4939

1036.jpg

14

4939

1037.jpg

15

4939

1038.jpg

16

5047

1045.jpg

17

5047

1046.jpg

18

5047

1047.jpg

19

5047

1048.jpg

20

5079

1051.jpg

21

5079

1057.jpg

22

5079

1058.jpg

23

5079

1059.jpg

24

5135

1060.jpg

25

5135

1061.jpg

26

5135

1062.jpg

27

5135

1063.jpg

28

5135

1064.jpg

29

5135

1065.jpg

30

5135

1066.jpg

31

5135

1067.jpg

32

5135

1070.jpg

33

5135

1071.jpg

34

5135

1072.jpg

35

5135

1073.jpg

36

5135

1074.jpg

37

5135

1079.jpg

Sheet5

Thank you in advance for all assistance. It is greatly appreciated.

Excel Facts

Why does 9 mean SUM in SUBTOTAL?

Click here to reveal answer

It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Sort by date Sort by votes

O

oldbrewer

Well-known Member

Apr 19, 2015

#2

it helps if you tell us what you are trying to do. It looks like for each id list all the .jpg files

[TABLE="width: 882"]
<colgroup><col span="2"><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4658[/TD]
[TD]1000.jpg[/TD]
[TD]1000.jpg[/TD]
[TD]=b21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4658[/TD]
[TD]1001.jpg[/TD]
[TD]1000.jpg;1001.jpg[/TD]
[TD="colspan: 4"]=IF(A22=A21,C21&";"&B22,B22)[/TD]
[TD="colspan: 2"]drag this down[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4658[/TD]
[TD]1002.jpg[/TD]
[TD]1000.jpg;1001.jpg;1002.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4658[/TD]
[TD]1004.jpg[/TD]
[TD]1000.jpg;1001.jpg;1002.jpg;1004.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4658[/TD]
[TD]1005.jpg[/TD]
[TD]1000.jpg;1001.jpg;1002.jpg;1004.jpg;1005.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4703[/TD]
[TD]1024.jpg[/TD]
[TD]1024.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4703[/TD]
[TD]1025.jpg[/TD]
[TD]1024.jpg;1025.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4703[/TD]
[TD]1026.jpg[/TD]
[TD]1024.jpg;1025.jpg;1026.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4913[/TD]
[TD]1031.jpg[/TD]
[TD]1031.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4913[/TD]
[TD]1032.jpg[/TD]
[TD]1031.jpg;1032.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4939[/TD]
[TD]1035.jpg[/TD]
[TD]1035.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4939[/TD]
[TD]1036.jpg[/TD]
[TD]1035.jpg;1036.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4939[/TD]
[TD]1037.jpg[/TD]
[TD]1035.jpg;1036.jpg;1037.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4939[/TD]
[TD]1038.jpg[/TD]
[TD]1035.jpg;1036.jpg;1037.jpg;1038.jpg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Upvote 0

shg

MrExcel MVP

Apr 19, 2015

#3

UDF?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][/tr][tr][td]

1

[/td][td]

ID

[/td][td]

Images

[/td][td]

Combination Images

[/td][td][/td][/tr]
[tr][td]

2

[/td][td]

4658

[/td][td]1000.jpg[/td][td]1000.jpg; 1001.jpg; 1002.jpg; 1004.jpg; 1005.jpg[/td][td]C2: =CatIf(INDEX($A$2:$A$37=A2, 0), $B$2:$B$37, "; ")[/td][/tr]
[tr][td]

3

[/td][td]

4658

[/td][td]1001.jpg[/td][td]1000.jpg; 1001.jpg; 1002.jpg; 1004.jpg; 1005.jpg[/td][td][/td][/tr]
[tr][td]

4

[/td][td]

4658

[/td][td]1002.jpg[/td][td]1000.jpg; 1001.jpg; 1002.jpg; 1004.jpg; 1005.jpg[/td][td][/td][/tr]
[tr][td]

5

[/td][td]

4658

[/td][td]1004.jpg[/td][td]1000.jpg; 1001.jpg; 1002.jpg; 1004.jpg; 1005.jpg[/td][td][/td][/tr]
[tr][td]

6

[/td][td]

4658

[/td][td]1005.jpg[/td][td]1000.jpg; 1001.jpg; 1002.jpg; 1004.jpg; 1005.jpg[/td][td][/td][/tr]
[tr][td]

7

[/td][td]

4703

[/td][td]1024.jpg[/td][td]1024.jpg; 1025.jpg; 1026.jpg[/td][td][/td][/tr]
[tr][td]

8

[/td][td]

4703

[/td][td]1025.jpg[/td][td]1024.jpg; 1025.jpg; 1026.jpg[/td][td][/td][/tr]
[tr][td]

9

[/td][td]

4703

[/td][td]1026.jpg[/td][td]1024.jpg; 1025.jpg; 1026.jpg[/td][td][/td][/tr]
[tr][td]

10

[/td][td]

4913

[/td][td]1031.jpg[/td][td]1031.jpg; 1032.jpg[/td][td][/td][/tr]
[tr][td]

11

[/td][td]

4913

[/td][td]1032.jpg[/td][td]1031.jpg; 1032.jpg[/td][td][/td][/tr]
[tr][td]

12

[/td][td]

4939

[/td][td]1035.jpg[/td][td]1035.jpg; 1036.jpg; 1037.jpg; 1038.jpg[/td][td][/td][/tr]
[tr][td]

13

[/td][td]

4939

[/td][td]1036.jpg[/td][td]1035.jpg; 1036.jpg; 1037.jpg; 1038.jpg[/td][td][/td][/tr]
[tr][td]

14

[/td][td]

4939

[/td][td]1037.jpg[/td][td]1035.jpg; 1036.jpg; 1037.jpg; 1038.jpg[/td][td][/td][/tr]
[tr][td]

15

[/td][td]

4939

[/td][td]1038.jpg[/td][td]1035.jpg; 1036.jpg; 1037.jpg; 1038.jpg[/td][td][/td][/tr]
[tr][td]

16

[/td][td]

5047

[/td][td]1045.jpg[/td][td]1045.jpg; 1046.jpg; 1047.jpg; 1048.jpg[/td][td][/td][/tr]
[tr][td]

17

[/td][td]

5047

[/td][td]1046.jpg[/td][td]1045.jpg; 1046.jpg; 1047.jpg; 1048.jpg[/td][td][/td][/tr]
[tr][td]

18

[/td][td]

5047

[/td][td]1047.jpg[/td][td]1045.jpg; 1046.jpg; 1047.jpg; 1048.jpg[/td][td][/td][/tr]
[tr][td]

19

[/td][td]

5047

[/td][td]1048.jpg[/td][td]1045.jpg; 1046.jpg; 1047.jpg; 1048.jpg[/td][td][/td][/tr]
[tr][td]

20

[/td][td]

5079

[/td][td]1051.jpg[/td][td]1051.jpg; 1057.jpg; 1058.jpg; 1059.jpg[/td][td][/td][/tr]
[tr][td]

21

[/td][td]

5079

[/td][td]1057.jpg[/td][td]1051.jpg; 1057.jpg; 1058.jpg; 1059.jpg[/td][td][/td][/tr]
[tr][td]

22

[/td][td]

5079

[/td][td]1058.jpg[/td][td]1051.jpg; 1057.jpg; 1058.jpg; 1059.jpg[/td][td][/td][/tr]
[tr][td]

23

[/td][td]

5079

[/td][td]1059.jpg[/td][td]1051.jpg; 1057.jpg; 1058.jpg; 1059.jpg[/td][td][/td][/tr]
[/table]

Code:

Function CatIf(avbIf As Variant, _
               rInp As Range, _
               Optional sSep As String = ",", _
               Optional bCatEmpty As Boolean = False) As String
  ' shg 2007
  ' UDF only

  ' Catenates the elements of rInp separated by sSep where the corresponding
  ' element of avbIf is True. Empty cells ignored unless bCatEmpty is True.

  Dim iRow          As Long
  Dim iCol          As Long
  Dim i             As Long

  On Error Resume Next
  i = UBound(avbIf, 2)

  If Err.Number Then
    ' avbIf is 1D
    For iRow = 1 To rInp.Rows.Count
      For iCol = 1 To rInp.Columns.Count
        i = i + 1
        If avbIf(i) Then
          If bCatEmpty Or Not IsEmpty(rInp(iRow, iCol).Value2) Then
            CatIf = CatIf & rInp(iRow, iCol).Value2 & sSep
          End If
        End If
      Next iCol
    Next iRow
  Else
    ' it's 2D
    For iRow = 1 To rInp.Rows.Count
      For iCol = 1 To rInp.Columns.Count
        If avbIf(iRow, iCol) Then
          If bCatEmpty Or Not IsEmpty(rInp(iRow, iCol).Value2) Then
            CatIf = CatIf & rInp(iRow, iCol).Value2 & sSep
          End If
        End If
      Next iCol
    Next iRow
  End If

  If Len(CatIf) Then CatIf = Left(CatIf, Len(CatIf) - Len(sSep))
End Function

Upvote 0

SOLTEC

Board Regular

Apr 19, 2015

#4

Each product has multiple images (.jpg) files that need to associated with that product. What this does is gives me all the product combination images. Red shirt, blue shirt, grey shirt, etc..

Upvote 0

SOLTEC

Board Regular

Apr 19, 2015

#5

I need all the .jpg files in all matching numbers, not concatenated each time OldBrewer please

Upvote 0

SOLTEC

Board Regular

Apr 19, 2015

#6

Please pardon me. I had three strokes in 2013 and I'm not the sharpest tool in the shed. How do I use your UDF Function please?

Upvote 0

shg

MrExcel MVP

Apr 19, 2015

#7

1. Copy the code from the post.

2. In Excel, do Alt + F11 to open the Visual Basic Editor.

3. From there, Insert > Module, and paste the code in the window that opens.

4. Do Alt+Q to return to Excel, and enter the formula as shown.

Then, VERY IMPORTANT, save the workbook with an xlsm extension.

Upvote 0

O

oldbrewer

Well-known Member

Apr 20, 2015

#8

with my approach easy to find last id number (with max number of jpegs) and copy to new location and repeat for all ids

Upvote 0

shg

MrExcel MVP

Apr 20, 2015

#9

I like oldbrewer's approach.

Upvote 0

O

oldbrewer

Well-known Member

Apr 20, 2015

#10

shg - in fact if say cell D3 has the max number of jpegs, a formula in say D100 should be able to find the ID (in C100) that is last in its block and bring back the jpeg string, probably using offset match.............

Upvote 0

You must log in or register to reply here.

Similar threads

U

Question

Create Data Base

Replies: 2

Views: 259

May 15, 2024

jolivanes

I

Solved

Current Code taking 10 Hours to run. Need Help to Optimize

Replies: 5

Views: 502

Feb 29, 2024

ItalianPlatinum

I

Question

Help to Optimize Macro Code for Faster Performance

Replies: 0

Views: 979

Jul 31, 2023

Guna13

M

Question

VBA Code - Run Code if cell value is not blank

Replies: 0

Views: 618

Aug 3, 2023

MHamid

M

Solved

If function with Countif formula

Replies: 3

Views: 445

Sep 5, 2022

6StringJazzer

Share:

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,223,228

Messages: 6,170,874

Members: 452,363

Latest member: merico17

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Top