Creating a string for multiple sets of data

adammon

New Member
Joined
Apr 8, 2015
Messages
14
Hi,

I'm wondering if there's an easy/automated way to make multiple strings of data from a large data set. The thing is i would like to use information in another cell to determine when a new string should be created. The issue that I'm having is coming up with a formula, or logic, to use a "start" and "stop" for the data that'll be used in to create the string(s). Below is an example from my data. I'd like for any cell in column B that has the same SKU in column A to be made into a string separated by commas.

AB
1combofilename
200639-TANABILENE-FEDORA_00639_TAN_01.jpg
303125-BROWNABRAHAM-VEST_03125_BROWN_01.jpg
403125-BROWNABRAHAM-VEST_03125_BROWN_10.jpg
503125-BROWNABRAHAM-VEST_03125_BROWN_11.jpg
603125-BROWNABRAHAM-VEST_03125_BROWN_12.jpg
703125-CHARCABRAHAM-VEST_03125_CHARC_01.jpg
803125-CHARCABRAHAM-VEST_03125_CHARC_10.jpg
903125-CHARCABRAHAM-VEST_03125_CHARC_11.jpg
1003125-CHARCABRAHAM-VEST_03125_CHARC_12.jpg
1103125-COPPRABRAHAM-VEST_03125_COPPR_01.jpg
1203125-COPPRABRAHAM-VEST_03125_COPPR_10.jpg
1303125-COPPRABRAHAM-VEST_03125_COPPR_11.jpg
1403125-COPPRABRAHAM-VEST_03125_COPPR_12.jpg
1502340-OFFWHACCELERATE-HOOD-FLEECE_02340_OFFWH_01.jpg
1600817-YELLWACCELERATE-HP-SNAPBACK_00817_YELLW_01.jpg
1706673-WAYELACCELERATE-L-S-POCKET-TEE_06673_WAYEL_01.jpg
1806673-WHITEACCELERATE-L-S-POCKET-TEE_06673_WHITE_01.jpg
1905208-MULTIACCELERATE-PIN-PACK_05208_MULTI_01.jpg
2006646-BLACKACCELERATE-S-S-STANDARD-TEE_06646_BLACK_01.jpg
2106646-ROYALACCELERATE-S-S-STANDARD-TEE_06646_ROYAL_01.jpg

<tbody>
</tbody>
Sheet1


Ideally, this is what it would look like after all said and done, but I'd be fine if it just repeated the information for however many instances there's duplicate information in column A. Meaning: rows 3 through 6 in my example above would still exist and the string created in column C, shown below, would be duplicated for each line (I hope that makes sense).

ABC
1combofilenameString
200639-TANABILENE-FEDORA_00639_TAN_01.jpgABILENE-FEDORA_00639_TAN_01.jpg
303125-BROWNABRAHAM-VEST_03125_BROWN_01.jpgABRAHAM-VEST_03125_BROWN_01.jpg,ABRAHAM-VEST_03125_BROWN_10.jpg,ABRAHAM-VEST_03125_BROWN_11.jpg,ABRAHAM-VEST_03125_BROWN_12.jpg
403125-CHARCABRAHAM-VEST_03125_CHARC_01.jpgABRAHAM-VEST_03125_CHARC_01.jpg,ABRAHAM-VEST_03125_CHARC_10.jpg,ABRAHAM-VEST_03125_CHARC_11.jpg,ABRAHAM-VEST_03125_CHARC_12.jpg
503125-COPPRABRAHAM-VEST_03125_COPPR_01.jpgABRAHAM-VEST_03125_COPPR_01.jpg,ABRAHAM-VEST_03125_COPPR_10.jpg,ABRAHAM-VEST_03125_COPPR_11.jpg,ABRAHAM-VEST_03125_COPPR_12.jpg
602340-OFFWHACCELERATE-HOOD-FLEECE_02340_OFFWH_01.jpgACCELERATE-HOOD-FLEECE_02340_OFFWH_01.jpg
700817-YELLWACCELERATE-HP-SNAPBACK_00817_YELLW_01.jpgACCELERATE-HP-SNAPBACK_00817_YELLW_01.jpg
806673-WAYELACCELERATE-L-S-POCKET-TEE_06673_WAYEL_01.jpgACCELERATE-L-S-POCKET-TEE_06673_WAYEL_01.jpg
906673-WHITEACCELERATE-L-S-POCKET-TEE_06673_WHITE_01.jpgACCELERATE-L-S-POCKET-TEE_06673_WHITE_01.jpg
1005208-MULTIACCELERATE-PIN-PACK_05208_MULTI_01.jpgACCELERATE-PIN-PACK_05208_MULTI_01.jpg
1106646-BLACKACCELERATE-S-S-STANDARD-TEE_06646_BLACK_01.jpgACCELERATE-S-S-STANDARD-TEE_06646_BLACK_01.jpg
1206646-ROYALACCELERATE-S-S-STANDARD-TEE_06646_ROYAL_01.jpgACCELERATE-S-S-STANDARD-TEE_06646_ROYAL_01.jpg

<tbody>
</tbody>
Sheet1
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG16Aug00
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dn.Offset(, 2).Value = Dn.Offset(, 1).Value
        .Add Dn.Value, Dn.Offset(, 1)
    [COLOR="Navy"]Else[/COLOR]
        .Item(Dn.Value).Offset(, 1).Value = .Item(Dn.Value) _
        .Offset(, 1).Value & ", " & Dn.Offset(, 1).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
    Columns("C:C").WrapText = True
    Rows.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,787
Office Version
365
Platform
Windows
In case you might be interested in a formula solution and you have the TEXTJOIN function you could try this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
ABC
1combofilename
200639-TANABILENE-FEDORA_00639_TAN_01.jpg
ABILENE-FEDORA_00639_TAN_01.jpg
303125-BROWNABRAHAM-VEST_03125_BROWN_01.jpgABRAHAM-VEST_03125_BROWN_01.jpg, ABRAHAM-VEST_03125_BROWN_10.jpg, ABRAHAM-VEST_03125_BROWN_11.jpg, ABRAHAM-VEST_03125_BROWN_12.jpg
403125-BROWNABRAHAM-VEST_03125_BROWN_10.jpg
503125-BROWNABRAHAM-VEST_03125_BROWN_11.jpg
603125-BROWNABRAHAM-VEST_03125_BROWN_12.jpg
703125-CHARCABRAHAM-VEST_03125_CHARC_01.jpgABRAHAM-VEST_03125_CHARC_01.jpg, ABRAHAM-VEST_03125_CHARC_10.jpg, ABRAHAM-VEST_03125_CHARC_11.jpg, ABRAHAM-VEST_03125_CHARC_12.jpg
803125-CHARCABRAHAM-VEST_03125_CHARC_10.jpg
903125-CHARCABRAHAM-VEST_03125_CHARC_11.jpg
1003125-CHARCABRAHAM-VEST_03125_CHARC_12.jpg
1103125-COPPRABRAHAM-VEST_03125_COPPR_01.jpgABRAHAM-VEST_03125_COPPR_01.jpg, ABRAHAM-VEST_03125_COPPR_10.jpg, ABRAHAM-VEST_03125_COPPR_11.jpg, ABRAHAM-VEST_03125_COPPR_12.jpg
1203125-COPPRABRAHAM-VEST_03125_COPPR_10.jpg
1303125-COPPRABRAHAM-VEST_03125_COPPR_11.jpg
1403125-COPPRABRAHAM-VEST_03125_COPPR_12.jpg
1502340-OFFWHACCELERATE-HOOD-FLEECE_02340_OFFWH_01.jpgACCELERATE-HOOD-FLEECE_02340_OFFWH_01.jpg
1600817-YELLWACCELERATE-HP-SNAPBACK_00817_YELLW_01.jpgACCELERATE-HP-SNAPBACK_00817_YELLW_01.jpg
1706673-WAYELACCELERATE-L-S-POCKET-TEE_06673_WAYEL_01.jpgACCELERATE-L-S-POCKET-TEE_06673_WAYEL_01.jpg
1806673-WHITEACCELERATE-L-S-POCKET-TEE_06673_WHITE_01.jpgACCELERATE-L-S-POCKET-TEE_06673_WHITE_01.jpg
1905208-MULTIACCELERATE-PIN-PACK_05208_MULTI_01.jpgACCELERATE-PIN-PACK_05208_MULTI_01.jpg
2006646-BLACKACCELERATE-S-S-STANDARD-TEE_06646_BLACK_01.jpgACCELERATE-S-S-STANDARD-TEE_06646_BLACK_01.jpg
2106646-ROYALACCELERATE-S-S-STANDARD-TEE_06646_ROYAL_01.jpgACCELERATE-S-S-STANDARD-TEE_06646_ROYAL_01.jpg
Multiple Sets
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,787
Office Version
365
Platform
Windows
If you don't want the blank rows then in the previous example you could filter column C for non-blanks or you could create a separate list as follows.

Excel Workbook
ABCDE
1combofilename
200639-TANABILENE-FEDORA_00639_TAN_01.jpg
00639-TAN
ABILENE-FEDORA_00639_TAN_01.jpg
303125-BROWNABRAHAM-VEST_03125_BROWN_01.jpg03125-BROWNABRAHAM-VEST_03125_BROWN_01.jpg, ABRAHAM-VEST_03125_BROWN_10.jpg, ABRAHAM-VEST_03125_BROWN_11.jpg, ABRAHAM-VEST_03125_BROWN_12.jpg
403125-BROWNABRAHAM-VEST_03125_BROWN_10.jpg03125-CHARCABRAHAM-VEST_03125_CHARC_01.jpg, ABRAHAM-VEST_03125_CHARC_10.jpg, ABRAHAM-VEST_03125_CHARC_11.jpg, ABRAHAM-VEST_03125_CHARC_12.jpg
503125-BROWNABRAHAM-VEST_03125_BROWN_11.jpg03125-COPPRABRAHAM-VEST_03125_COPPR_01.jpg, ABRAHAM-VEST_03125_COPPR_10.jpg, ABRAHAM-VEST_03125_COPPR_11.jpg, ABRAHAM-VEST_03125_COPPR_12.jpg
603125-BROWNABRAHAM-VEST_03125_BROWN_12.jpg02340-OFFWHACCELERATE-HOOD-FLEECE_02340_OFFWH_01.jpg
703125-CHARCABRAHAM-VEST_03125_CHARC_01.jpg00817-YELLWACCELERATE-HP-SNAPBACK_00817_YELLW_01.jpg
803125-CHARCABRAHAM-VEST_03125_CHARC_10.jpg06673-WAYELACCELERATE-L-S-POCKET-TEE_06673_WAYEL_01.jpg
903125-CHARCABRAHAM-VEST_03125_CHARC_11.jpg06673-WHITEACCELERATE-L-S-POCKET-TEE_06673_WHITE_01.jpg
1003125-CHARCABRAHAM-VEST_03125_CHARC_12.jpg05208-MULTIACCELERATE-PIN-PACK_05208_MULTI_01.jpg
1103125-COPPRABRAHAM-VEST_03125_COPPR_01.jpg06646-BLACKACCELERATE-S-S-STANDARD-TEE_06646_BLACK_01.jpg
1203125-COPPRABRAHAM-VEST_03125_COPPR_10.jpg06646-ROYALACCELERATE-S-S-STANDARD-TEE_06646_ROYAL_01.jpg
1303125-COPPRABRAHAM-VEST_03125_COPPR_11.jpg
1403125-COPPRABRAHAM-VEST_03125_COPPR_12.jpg
1502340-OFFWHACCELERATE-HOOD-FLEECE_02340_OFFWH_01.jpg
1600817-YELLWACCELERATE-HP-SNAPBACK_00817_YELLW_01.jpg
1706673-WAYELACCELERATE-L-S-POCKET-TEE_06673_WAYEL_01.jpg
1806673-WHITEACCELERATE-L-S-POCKET-TEE_06673_WHITE_01.jpg
1905208-MULTIACCELERATE-PIN-PACK_05208_MULTI_01.jpg
2006646-BLACKACCELERATE-S-S-STANDARD-TEE_06646_BLACK_01.jpg
2106646-ROYALACCELERATE-S-S-STANDARD-TEE_06646_ROYAL_01.jpg
Multiple Sets (2)
 

Watch MrExcel Video

Forum statistics

Threads
1,100,192
Messages
5,473,067
Members
406,844
Latest member
mango22

This Week's Hot Topics

Top