group duplicates in excel

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>
 

Some videos you may like

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.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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.
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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.
 

eliadrin

New Member
Joined
Jul 29, 2010
Messages
10
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)
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,805
Messages
5,513,512
Members
408,955
Latest member
BodanKaraoke

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top