Removing Duplicates - But Retain Results

[CDNWolf]

Hi guys, I've looked for a simple solution but come up with nothing;
This list can be long and manual manipulation is not ideal...

I have a list of names and tasks completed as per attached image
I want to remove the duplicate names but retain the task count beside the names transposed accross each column;

For example;
Mike 5 8 8
David 8 7
Josh 7 2...


Thanks in advance for your assistance

 

[BrianM45]

Hi CDNWolf,

How you want it : formula (maybe impossible), VBA, Power Query ?

 

[CDNWolf]

Hi CDNWolf,

How you want it : formula (maybe impossible), VBA, Power Query ?

Brian,

Thanks for your reply

I have no preference but my VBA knowledge is pretty basic.
What are your thoughts?

 

[CDNWolf]

What about using a Pivot Table

 

[mohadin]

Hi
Waht about

Sub test()
Dim a, m
Dim i&
a = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1).Resize(, 2)
With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
        If Not .exists(a(i, 1)) Then
        .Add a(i, 1), a(i, 2)
        Else
        .Item(a(i, 1)) = .Item(a(i, 1)) & "|" & a(i, 2)
        End If
      Next
      Cells(2, 5).Resize(.Count, 2) = Application.Transpose(Application.Index(Array(.keys, .items), 0, 0))
      Cells(2, 6).Resize(.Count).TextToColumns Cells(2, 6), 1, , , , , , , True, "|"
          End With
End Sub

 

[Peter_SSs]

@CDNWolf
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also helps to give sample data in a form that helpers can easily copy for testing:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Peter_SSs]

.. what Excel version(s) & platform(s) you are using ..

So, if you have the latest functions, this might be one possibility.

23 03 04.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		B	1		B	1	2	1	6
3		A	8		A	8
4		F	6		F	6	3	2	4	1
5		F	3		D	8	5
6		F	2		E	6	3
7		F	4
8		D	8
9		F	1
10		D	5
11		E	6
12		B	2
13		E	3
14		B	1
15		B	6
CDNWolf

Cell Formulas
Range	Formula
E2:E6	E2	=UNIQUE(B2:B15)
F2:I2,F5:G6,F4:J4,F3,F7:F9	F2	=TRANSPOSE(FILTER(C$2:C$15,B$2:B$15=E2,""))
Dynamic array formulas.