Concatenate for n Number text, if my condition matches more than twice

[nianchi111]

Hi all,

could anyone please help me to sort the below question.

A table contains List of project, Benefits and complexity and another table contains Headings and rows as Complexity and Benefits

how could i get ans like in the table 2:

For Eg:
Table 1:
Projects Benefits Complexity
Project1 High Easy
Project2 High Moderate
Project3 High Difficult
Project4 High Easy
Project5 High Moderate
Project6 High Difficult
Project7 Medium Easy
Project8 Medium Moderate
Project9 Medium Difficult
Project10 Medium Easy
Project11 Medium Moderate
Project12 Medium Difficult

Table 2:
Benefits/Complexity | Easy | Moderate | Difficult
High | Project1,Project4 |Project2,Project5 | Project3,Project6
Medium | Project7,Project10 |Project8,Project11 | Project9,Project12

Could anyone please help me sort out this question?

 

[MickG]

With line "Projects Benefits Complexity" on row 1 starting "A1".
Try this for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG16Apr24
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Ray()
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ohds        [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] k           [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
   [COLOR="Navy"]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, 1).Value), Dn.Offset(, -1).Value
        [COLOR="Navy"]Else[/COLOR]
         Dic(Dn.Value).Item(Dn.Offset(, 1).Value) = Dic(Dn.Value).Item(Dn.Offset(, 1).Value) _
         & "," & Dn.Offset(, -1).Value
       [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
  
   
Ohds = Array("Benefits/Complexity", "Easy", "Moderate", "Difficult")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
        c = c + 1
        ReDim Preserve Ray(1 To 4, 1 To c)
        Ray(1, c) = k
        [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ohds)
            [COLOR="Navy"]If[/COLOR] Dic(k).exists(Ohds(n)) [COLOR="Navy"]Then[/COLOR]
            Ray(n + 1, c) = Dic(k).Item(Ohds(n))
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
   [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    .Range("A1").Resize(, 4) = Ohds
    .Range("A2").Resize(c, 4) = Application.Transpose(Ray)
    [COLOR="Navy"]With[/COLOR] .Range("A1").Resize(c + 1, 4)
        .Borders.Weight = 2
        .Columns.AutoFit
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Peter_SSs]

If you have Excel through Office 365, you can do it with a formula like 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 across & down.

Excel Workbook
	A	B	C	D
1	Projects	Benefits	Complexity
2	Project1	High	Easy
3	Project2	High	Moderate
4	Project3	High	Difficult
5	Project4	High	Easy
6	Project5	High	Moderate
7	Project6	High	Difficult
8	Project7	Medium	Easy
9	Project8	Medium	Moderate
10	Project9	Medium	Difficult
11	Project10	Medium	Easy
12	Project11	Medium	Moderate
13	Project12	Medium	Difficult
14
15
16	Benefits/Complexity	Easy	Moderate	Difficult
17	High	Project1,Project4	Project2,Project5	Project3,Project6
18	Medium	Project7,Project10	Project8,Project11	Project9,Project12
TEXTJOIN

 

[nianchi111]

Hi Mick

Thank you, your code gives me the exact answer.

Could you please explain me or refer me to know more about Scripting.Dictionary and it keys

Thank you

 

[nianchi111]

Hi Peter,

Thank you for your response, But when i am trying this Formula i am receiving an error as #Value ,

Could you please guide me, where i am making mistake, fyi i have tried CTRL+SHIFT+Enter and i can view {} in formula bar, but in cell it shows as #Value

 

[Peter_SSs]

Hi Peter,

Thank you for your response, But when i am trying this Formula i am receiving an error as [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] ,

Could you please guide me, where i am making mistake, fyi i have tried CTRL+SHIFT+Enter and i can view {} in formula bar, but in cell it shows as [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL]

You can see from my screen shot that it works for the sample data. Perhaps your real data is different in some way.

Two ways that the formula could return #VALUE that I can immediately think of:
- You already have some #VALUE results in the original data table (possible?)
- The result of the TEXTJOIN function would exceed 32,767 characters (possible?)

As a first step, if you set up a new blank sheet with small sample data like post #3 , can you get it to work?

Does the original table contain formulas, or just 'constant' text values?

 

[MickG]

This might be a good starting point :-
http://www.snb-vba.eu/VBA_Dictionary_en.html