Creating a string for multiple sets of data

A

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
combofilename
00639-TANABILENE-FEDORA_00639_TAN_01.jpg
03125-BROWNABRAHAM-VEST_03125_BROWN_01.jpg
03125-BROWNABRAHAM-VEST_03125_BROWN_10.jpg
03125-BROWNABRAHAM-VEST_03125_BROWN_11.jpg
03125-BROWNABRAHAM-VEST_03125_BROWN_12.jpg
03125-CHARCABRAHAM-VEST_03125_CHARC_01.jpg
03125-CHARCABRAHAM-VEST_03125_CHARC_10.jpg
03125-CHARCABRAHAM-VEST_03125_CHARC_11.jpg
03125-CHARCABRAHAM-VEST_03125_CHARC_12.jpg
03125-COPPRABRAHAM-VEST_03125_COPPR_01.jpg
03125-COPPRABRAHAM-VEST_03125_COPPR_10.jpg
03125-COPPRABRAHAM-VEST_03125_COPPR_11.jpg
03125-COPPRABRAHAM-VEST_03125_COPPR_12.jpg
02340-OFFWHACCELERATE-HOOD-FLEECE_02340_OFFWH_01.jpg
00817-YELLWACCELERATE-HP-SNAPBACK_00817_YELLW_01.jpg
06673-WAYELACCELERATE-L-S-POCKET-TEE_06673_WAYEL_01.jpg
06673-WHITEACCELERATE-L-S-POCKET-TEE_06673_WHITE_01.jpg
05208-MULTIACCELERATE-PIN-PACK_05208_MULTI_01.jpg
06646-BLACKACCELERATE-S-S-STANDARD-TEE_06646_BLACK_01.jpg
06646-ROYALACCELERATE-S-S-STANDARD-TEE_06646_ROYAL_01.jpg

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

</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
combofilenameString
00639-TANABILENE-FEDORA_00639_TAN_01.jpgABILENE-FEDORA_00639_TAN_01.jpg
03125-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
03125-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
03125-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
02340-OFFWHACCELERATE-HOOD-FLEECE_02340_OFFWH_01.jpgACCELERATE-HOOD-FLEECE_02340_OFFWH_01.jpg
00817-YELLWACCELERATE-HP-SNAPBACK_00817_YELLW_01.jpgACCELERATE-HP-SNAPBACK_00817_YELLW_01.jpg
06673-WAYELACCELERATE-L-S-POCKET-TEE_06673_WAYEL_01.jpgACCELERATE-L-S-POCKET-TEE_06673_WAYEL_01.jpg
06673-WHITEACCELERATE-L-S-POCKET-TEE_06673_WHITE_01.jpgACCELERATE-L-S-POCKET-TEE_06673_WHITE_01.jpg
05208-MULTIACCELERATE-PIN-PACK_05208_MULTI_01.jpgACCELERATE-PIN-PACK_05208_MULTI_01.jpg
06646-BLACKACCELERATE-S-S-STANDARD-TEE_06646_BLACK_01.jpgACCELERATE-S-S-STANDARD-TEE_06646_BLACK_01.jpg
06646-ROYALACCELERATE-S-S-STANDARD-TEE_06646_ROYAL_01.jpgACCELERATE-S-S-STANDARD-TEE_06646_ROYAL_01.jpg

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

</tbody>
Sheet1​
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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
 
Upvote 0
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.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-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
 
Upvote 0
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.jpg00639-TANABILENE-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)
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top