Results 1 to 2 of 2

Thread: Expanding on 'concatenate-maybe-countif-but-how'
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Expanding on 'concatenate-maybe-countif-but-how'

    Dear Masters of Excel,

    Expanding on an old thread - http://www.mrexcel.com/forum/excel-q...f-but-how.html - I could do with a little help.

    Mr Excel offers the macro;

    Sub
    Rearrange()
    Dim lr AsLong, r AsLong

    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For r = lr To 2 Step -1
    With Cells(r, 1)
    If .Value = .Offset(-1).Value Then
    .Offset(-1, 1).Value = .Offset(-1, 1).Value & ", " _
    & .Offset(, 1).Value
    .EntireRow.Delete
    EndIf
    EndWith
    Next r
    Columns("B").AutoFit
    Application.ScreenUpdating = True
    EndSub

    This works great for combining fields in a single column. However, I need to combine 2 columns, I need to turn this;

    Room number Furniture Type Count
    1 Table 3
    1 Chair 3
    1 Cat 1
    2 Rug 23
    3 Planet 1
    3 Chair 6

    into this;
    Room Number
    1 Table 3, Chair 3, Cat 1
    2 Rug 23
    3 Planet 1, Chair 6


    Any help at all would be really great...

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,985
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Expanding on 'concatenate-maybe-countif-but-how'

    =sumproduct((a1:a6=1)*(B1:b6=table)) returns 3

    instead of the 1 and table in that formula, reference row and column headings and then adjust the looks of your output table. Very simple approach.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •