group duplicates in excel

E

eliadrin

New Member
Joined
Jul 29, 2010
Messages
10
Hi All,
With your kind help I need to figure out a way to group information to create a new worksheet,organized in a different way then what I have now.
As shown in the below example , I need to first look at column B to find duplicates, than to put whatever is in column A of the duplicate in the row next to B.
If you look all the way down, rows 38,39 is manually done what I'm looking for a trick to do(on about 6000 records,and it cannot be done manually)
The end result will be the content of B and next to it side by side in a row the content of all matching A's

Thank you in advance
<table style="width: 273px; height: 1014px;" border="2" cellspacing="0"><tbody><tr valign="bottom"><th bgcolor="#b0b0b0" width="22">
</th><th align="center" bgcolor="#b0b0b0" width="76">A</th><th align="center" bgcolor="#b0b0b0" width="73">B</th><th align="center" bgcolor="#b0b0b0" width="73">C</th><th align="center" bgcolor="#b0b0b0" width="73">D</th><th align="center" bgcolor="#b0b0b0" width="73">E</th></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">1</th><td width="61">xlm1</td><td width="59">yes-0-1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">2 </th><td width="61">xlm2</td><td width="59">to 13.19</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">3 </th><td width="61">xlm3</td><td width="59">old 15-6</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">4 </th><td width="61">mrc5</td><td width="59">old 1218.3,1,1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">5 </th><td width="61">xlm5</td><td width="59">ert35-13</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">6 </th><td width="61">mrc13</td><td width="59">yes-0-1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">7 </th><td width="61">xlm7</td><td width="59">trd*r-1-2-0</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">8 </th><td width="61">wsa23</td><td width="59">ert35-13</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">9 </th><td width="61">xlm9</td><td width="59">no-110</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">10 </th><td width="61">obp12</td><td width="59">to 13.19</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">11 </th><td width="61">wsd19</td><td width="59">old 1218.3,1,1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">12 </th><td width="61">xlm12</td><td width="59">ert35-13</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">13 </th><td width="61">xlm13</td><td width="59">trd*r-1-2-0</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">14 </th><td width="61">trc2</td><td width="59">to 13.19</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">15 </th><td width="61">erf90</td><td width="59">ert35-13</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">16 </th><td width="61">trc4</td><td width="59">no-110</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">17 </th><td width="61">trc5</td><td width="59">tyn-bbs-1-1-1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">18 </th><td width="61">trc6</td><td width="59">yes-0-1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">19 </th><td width="61">trc7</td><td width="59">Bash13-0-1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">20 </th><td width="61">lbt6</td><td width="59">trd*r-1-2-0</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">21 </th><td width="61">trc9</td><td width="59">old 1218.3,1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">22 </th><td width="61">trc10</td><td width="59">tyn-bbs-1-1-1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">23 </th><td width="61">trc11</td><td width="59">Bash13-0-1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">24 </th><td width="61">ynt5</td><td width="59">old 1218.3,1,1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">25 </th><td width="61">trc13</td><td width="59">shell3.1.1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">26 </th><td width="61">trc14</td><td width="59">no-110</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">27 </th><td width="61">ltt9</td><td width="59">ert35-13</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">28 </th><td width="61">ltt10</td><td width="59">yes-0-1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">29 </th><td width="61">ynt3</td><td width="59">Bash13-0-1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">30 </th><td width="61">ltt12</td><td width="59">old 13.3,1,1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">31 </th><td width="61">xtr008</td><td width="59">old 1218.3,1</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">32 </th><td width="61">ltt14</td><td width="59">old 15-6</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">33 </th><td width="61">xtn001</td><td width="59">no-110</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">34 </th><td width="61">ltt16</td><td width="59">to 13.19</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">35 </th><td width="61">
</td><td width="59">
</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">36 </th><td width="61">
</td><td width="59">
</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">37 </th><td width="61">
</td><td width="59">
</td><td width="59">
</td><td width="59">
</td><td width="59">
</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">38 </th><td width="61">yes-0-1</td><td width="59">xlm1</td><td width="59">mrc13</td><td width="59">trc6</td><td width="59">ltt10</td></tr><tr valign="bottom"><th align="center" bgcolor="#b0b0b0" height="12" width="18">39</th><td width="61">old 1218.3,1,1</td><td width="59">mrc5</td><td width="59">wsd19</td><td width="59">ynt5</td><td width="59">
</td></tr></tbody></table>
​
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
eliadrin,


Welcome to the MrExcel forum.


Sample data before the macro:


Excel Workbook
ABCDEFGHI
1xlm1yes-0-1
2xlm2to 13.19
3xlm3old 15-6
4mrc5old 1218.3,1,1
5xlm5ert35-13
6mrc13yes-0-1
7xlm7trd*r-1-2-0
8wsa23ert35-13
9xlm9no-110
10obp12to 13.19
11wsd19old 1218.3,1,1
12xlm12ert35-13
13xlm13trd*r-1-2-0
14trc2to 13.19
15erf90ert35-13
16trc4no-110
17trc5tyn-bbs-1-1-1
18trc6yes-0-1
19trc7Bash13-0-1
20lbt6trd*r-1-2-0
21trc9old 1218.3,1
22trc10tyn-bbs-1-1-1
23trc11Bash13-0-1
24ynt5old 1218.3,1,1
25trc13shell3.1.1
26trc14no-110
27ltt9ert35-13
28ltt10yes-0-1
29ynt3Bash13-0-1
30ltt12old 13.3,1,1
31xtr008old 1218.3,1
32ltt14old 15-6
33xtn001no-110
34ltt16to 13.19
35
Sheet1





After the macro:


Excel Workbook
ABCDEFGHI
1xlm1yes-0-1yes-0-1xlm1mrc13trc6ltt10
2xlm2to 13.19to 13.19xlm2obp12trc2ltt16
3xlm3old 15-6old 15-6xlm3ltt14
4mrc5old 1218.3,1,1old 1218.3,1,1mrc5wsd19ynt5
5xlm5ert35-13ert35-13xlm5wsa23xlm12erf90ltt9
6mrc13yes-0-1trd*r-1-2-0xlm7xlm13lbt6
7xlm7trd*r-1-2-0no-110xlm9trc4trc14xtn001
8wsa23ert35-13tyn-bbs-1-1-1trc5trc10
9xlm9no-110Bash13-0-1trc7trc11ynt3
10obp12to 13.19old 1218.3,1trc9xtr008
11wsd19old 1218.3,1,1shell3.1.1trc13
12xlm12ert35-13old 13.3,1,1ltt12
13xlm13trd*r-1-2-0
14trc2to 13.19
15erf90ert35-13
16trc4no-110
17trc5tyn-bbs-1-1-1
18trc6yes-0-1
19trc7Bash13-0-1
20lbt6trd*r-1-2-0
21trc9old 1218.3,1
22trc10tyn-bbs-1-1-1
23trc11Bash13-0-1
24ynt5old 1218.3,1,1
25trc13shell3.1.1
26trc14no-110
27ltt9ert35-13
28ltt10yes-0-1
29ynt3Bash13-0-1
30ltt12old 13.3,1,1
31xtr008old 1218.3,1
32ltt14old 15-6
33xtn001no-110
34ltt16to 13.19
35
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).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your 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. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub GroupDups()
' hiker95, 08/05/2010, ME486458
Dim LR As Long, NC As Long, MC As Long
Dim c As Range, d As Range, firstaddress As String
Application.ScreenUpdating = False
Range("A1").EntireRow.Insert
Range("B1") = "Test"
LR = Cells(Rows.Count, "B").End(xlUp).Row
Range("B1:B" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("D1"), Unique:=True
For Each d In Range("D2", Range("D" & Rows.Count).End(xlUp))
  NC = 4
  With Columns(2)
    Set c = .Find(d, LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
      firstaddress = c.Address
      Do
        NC = NC + 1
        If NC > 256 Then
          Range("A1").EntireRow.Delete
          Application.ScreenUpdating = True
          MsgBox "There are no more columns to the right for your data - macro terminated!"
          Exit Sub
        End If
        If NC > MC Then MC = NC
        Cells(d.Row, NC) = c.Offset(, -1)
        Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
  End With
Next d
Range("A1").EntireRow.Delete
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub


Then run the "GroupDups" macro.


The macro will stop if it attemtps to write past column IV. So, there can not be more that 252 items in column A, for and of the unique text in column D.
 
Upvote 0
Or with formulas:

Before:
Excel Workbook
AB
1PersonPart #
2xlm1yes-0-1
3xlm2to 13.19
4xlm3old 15-6
5mrc5old 1218.3,1,1
6xlm5ert35-13
7mrc13yes-0-1
8xlm7trd*r-1-2-0
9wsa23ert35-13
10xlm9no-110
11obp12to 13.19
12wsd19old 1218.3,1,1
13xlm12ert35-13
14xlm13trd*r-1-2-0
15trc2to 13.19
16erf90ert35-13
17trc4no-110
18trc5tyn-bbs-1-1-1
19trc6yes-0-1
20trc7Bash13-0-1
21lbt6trd*r-1-2-0
22trc9old 1218.3,1
23trc10tyn-bbs-1-1-1
24trc11Bash13-0-1
25ynt5old 1218.3,1,1
26trc13shell3.1.1
27trc14no-110
28ltt9ert35-13
29ltt10yes-0-1
30ynt3Bash13-0-1
...
Excel Workbook
AB
31ltt12old 13.3,1,1
32xtr008old 1218.3,1
33ltt14old 15-6
34xtn001no-110
35ltt16to 13.19
...


After:
Excel Workbook
DEFGHIJK
1Count UniquePart #CountitemReturned1Returned2Returned3Returned4Returned5
2124yes-0-1xlm1mrc13trc6ltt10
34to 13.19xlm2obp12trc2ltt16
42old 15-6xlm3ltt14
53old 1218.3,1,1mrc5wsd19ynt5
65ert35-13xlm5wsa23xlm12erf90ltt9
73trd*r-1-2-0xlm7xlm13lbt6
84no-110xlm9trc4trc14xtn001
92tyn-bbs-1-1-1trc5trc10
103Bash13-0-1trc7trc11ynt3
112old 1218.3,1trc9xtr008
121shell3.1.1trc13
131old 13.3,1,1ltt12
14
15
16
...


Formulas:
Excel Workbook
D
1Count UniquePart #
212
...
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Excel Workbook
E
1Count
24
...
Cell Formulas
RangeFormula
E2=IF(F2="","",COUNTIF($B$2:$B$35,F2))


Copy that one down.
Excel Workbook
F
1item
2yes-0-1
...
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Copy that one down.
Excel Workbook
G
1Returned1
2xlm1
...
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Copy that to the range G2:K16.
 
Upvote 0
I can't thank you both, enough.
Both methods runs perfectly,
(if I could only understand what I was doing.....but that's a different story)
 
Upvote 0
Dear eliadrin,

For that different story you were referring to:

If you want to study and learn array formulas, something that takes time, try these:

To learn about the basics of array formulas, see this playlist of videos:
http://www.youtube.com/view_play_list?p=007E7E9CA63304D3

To learn the basics of array formula data extract formulas, see these three 3 videos:
http://www.youtube.com/watch?v=Tp7I5u1MqiM
http://www.youtube.com/watch?v=R5ZWAiNJLNo
http://www.youtube.com/watch?v=132ZdpxBm1U

To learn the basics of Unique Record Counting and Data Extract formulas, see this video:
http://www.youtube.com/watch?v=uUrI8hoj8BA
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Picture won't paste in cell overlay
Replies
0
Views
59
Cerka
C
F
  • Question
Need a formula.
Replies
1
Views
289
Peter_SSs
Peter_SSs
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
299
awoohaw
awoohaw
E
  • Question
VBA for Web Scraping help
Replies
0
Views
579
epactheactor
E
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
139
dilshod_k
D

Forum statistics

Threads
1,214,522
Messages
6,120,020
Members
448,939
Latest member
Leon Leenders

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