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

nianchi111

Board Regular
Joined
Aug 24, 2007
Messages
181
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?
 
Last edited:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
With line "Projects Benefits Complexity" on row 1 starting "A1".
Try this for results on sheet2.
Code:
[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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,244
Office Version
365
Platform
Windows
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
ABCD
1ProjectsBenefitsComplexity
2Project1HighEasy
3Project2HighModerate
4Project3HighDifficult
5Project4HighEasy
6Project5HighModerate
7Project6HighDifficult
8Project7MediumEasy
9Project8MediumModerate
10Project9MediumDifficult
11Project10MediumEasy
12Project11MediumModerate
13Project12MediumDifficult
14
15
16Benefits/ComplexityEasyModerateDifficult
17High Project2,Project5Project3,Project6
18MediumProject7,Project10Project8,Project11Project9,Project12
TEXTJOIN
 

nianchi111

Board Regular
Joined
Aug 24, 2007
Messages
181
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

Board Regular
Joined
Aug 24, 2007
Messages
181
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,244
Office Version
365
Platform
Windows
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?
 
Last edited:

Forum statistics

Threads
1,082,040
Messages
5,362,804
Members
400,693
Latest member
jenlj

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top