code or formula for table

S

shibujohn

New Member
Joined
Nov 23, 2014
Messages
2
Pls guide a code or formula from below tables, table1 is data and table2 is the ouput which needs to generated... The task and codes will increase as new ones will be added...

Table1 Table2

[TABLE="width: 307"]
<tbody>[TR]
[TD="width: 124, bgcolor: #00B0F0"]Task[/TD]
[TD="width: 64, bgcolor: #00B0F0"]Code[/TD]
[TD="width: 157, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Garden[/TD]
[TD="bgcolor: transparent, align: right"]231[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Garden=231,234,412,311,987
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Garden[/TD]
[TD="bgcolor: transparent, align: right"]234[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Kitch=534,453,459,434,745
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Garden[/TD]
[TD="bgcolor: transparent, align: right"]412[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Terrace=532,538,744[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Garden[/TD]
[TD="bgcolor: transparent, align: right"]311[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Garden[/TD]
[TD="bgcolor: transparent, align: right"]987[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kitch[/TD]
[TD="bgcolor: transparent, align: right"]534[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kitch[/TD]
[TD="bgcolor: transparent, align: right"]453[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kitch[/TD]
[TD="bgcolor: transparent, align: right"]459[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kitch[/TD]
[TD="bgcolor: transparent, align: right"]434[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kitch[/TD]
[TD="bgcolor: transparent, align: right"]745[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Terrace[/TD]
[TD="bgcolor: transparent, align: right"]532[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Terrace[/TD]
[TD="bgcolor: transparent, align: right"]538[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Terrace[/TD]
[TD="bgcolor: transparent, align: right"]744[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
shibujohn,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Each time you add new data in columns A, and, B, and, run the macro again, the old results will be deleted, and, the new results will be displayed for the new dataset in columns A, and, B.


Sample raw data:


Excel 2007
ABCDEF
1TaskCode
2Garden231
3Garden234
4Garden412
5Garden311
6Garden987
7Kitch534
8Kitch453
9Kitch459
10Kitch434
11Kitch745
12Terrace532
13Terrace538
14Terrace744
15
Sheet1


After the macro with a slightly different result format:


Excel 2007
ABCDEF
1TaskCode
2Garden231Garden231,234,412,311,987
3Garden234Kitch534,453,459,434,745
4Garden412Terrace532,538,744
5Garden311
6Garden987
7Kitch534
8Kitch453
9Kitch459
10Kitch434
11Kitch745
12Terrace532
13Terrace538
14Terrace744
15
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub GetUniquesConcat()
' hiker95, 11/23/2014, ME820031
Dim rng As Range, c As Range
Columns("E:F").ClearContents
Set rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each c In rng
    If Not .Exists(c.Value) Then
      .Add c.Value, c.Offset(, 1)
    Else
      .Item(c.Value) = .Item(c.Value) & "," & c.Offset(, 1)
    End If
  Next
  Range("F2").Resize(.Count).NumberFormat = "@"
  Range("E2").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .Items))
  Columns("E:F").AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetUniquesConcat macro.
 
Upvote 0
I turned the range into a table called "Table1" so that as new rows are updated, the formulas update

Excel 2010
ABCDE
1TaskCode
2Garden231Garden231,234,412,311,987
3Garden234Kitch534,453,459,434,745,523
4Garden412Terrace532,538,744
5Garden311Cabin
6Garden987
7Kitch534
8Kitch453
9Kitch459
10Kitch434
11Kitch745
12Terrace532
13Terrace538
14Terrace744
15Kitch523
Sheet8
Cell Formulas
RangeFormula
E2=Addlikes(Table1[#All],$D2)


Here's the UDF i use
Code: 
Function Addlikes(Rng As Range, Tofind As String)
    Dim i As Integer
    For i = 1 To Rng.Rows.Count
        If Rng.Cells(i, 1) = Tofind Then
            Addlikes = Addlikes & "," & Rng.Cells(i, 2).Value
        End If
    Next i
    If Len(Addlikes) < 2 Then
        Addlikes = ""
    Else
        Addlikes = Right(Addlikes, Len(Addlikes) - 1)
    End If
End Function
 
Upvote 0
shibujohn,

Here is another macro solution for you to consider.

With your raw data in column A, sorted/grouped by Task.

After the new macro:


Excel 2007
ABCDE
1TaskCode
2Garden231Garden=231,234,412,311,987
3Garden234Kitch=534,453,459,434,745
4Garden412Terrace=532,538,744
5Garden311
6Garden987
7Kitch534
8Kitch453
9Kitch459
10Kitch434
11Kitch745
12Terrace532
13Terrace538
14Terrace744
15
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Sub GetUniquesConcat_V2()
' hiker95, 11/23/2014, ME820031
Dim r As Long, lr As Long, nr As Long, n As Long
Application.ScreenUpdating = False
Columns(5).ClearContents
lr = Cells(Rows.Count, 1).End(xlUp).Row
nr = 1
For r = 2 To lr
  n = Application.CountIf(Columns(1), Cells(r, 1).Value)
  nr = nr + 1
  Cells(nr, 5) = Cells(r, 1) & "=" & Join(Application.Transpose(Range("B" & r & ":B" & r + n - 1)), ",")
  r = r + n - 1
Next r
Columns(5).AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetUniquesConcat_V2 macro.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
how to random all numbers
Replies
7
Views
270
Fluff
Fluff
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
432
johnnyL
J
S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D
S
  • Solved
Find the missing numbers from the range of numbers and filling the missing number in the cells below the last row
Replies
6
Views
598
sekar
S
E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
289
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

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