Condense multiples into first occurrence only

nguerra

New Member
Joined
Oct 1, 2013
Messages
46
Is there a way to take the first occurrence in every value change in a column and re-write it with another value in the same row? For example:

Column 1Column 2Column 6Column 7
appleaapplea
appleapeara
appleaorangeb
pearagrapea
pearabananab
orangebappleb
grapeaorangea
grapea
bananab
appleb
appleb
orangea

<colgroup><col style="width:54pt" span="6" width="72"> </colgroup><tbody>
</tbody>
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Is there a way to take the first occurrence in every value change in a column and re-write it with another value in the same row? For example:

Column 1Column 2Column 6Column 7
appleaapplea
appleapeara
appleaorangeb
pearagrapea
pearabananab
orangebappleb
grapeaorangea
grapea
bananab
appleb
appleb
orangea

<colgroup><col style="width:54pt" span="6" width="72"> </colgroup><tbody>
</tbody>

Hi Nguerra,
While I'm working on it I have a question, could something like this appear in the table?

Apple a

Apple a

Apple a

Apple b

===========================================================


ZAX
 
Upvote 0
Zax, that is an excellent question. After thinking about it, it could happen but that would be so rare that I would consider it to be an oddity.
 
Upvote 0
nguerra,

Sample raw data:


Excel 2007
ABCDEFG
1applea
2applea
3applea
4peara
5peara
6orangeb
7grapea
8grapea
9bananab
10appleb
11appleb
12orangea
13
Sheet1


After the macro:


Excel 2007
ABCDEFG
1appleaapplea
2appleapeara
3appleaorangeb
4pearagrapea
5pearabananab
6orangebappleb
7grapeaorangea
8grapea
9bananab
10appleb
11appleb
12orangea
13
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:
Option Explicit
Sub GetUniquePairs()
' hiker95, 02/19/2014, ME758897
Application.ScreenUpdating = False
Rows(1).Insert
Cells(1, 1).Resize(, 2).Value = Array("A", "B")
Columns("A:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns("F:G"), Unique:=True
Rows(1).Delete
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 GetUniquePairs macro.
 
Upvote 0
Try this:
Code:
Sub CopyOneInstance()
Dim Cell As Range
For Each Cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If Cell.Value <> Cell.Offset(1, 0).Value Or Cell.Offset(0, 1).Value <> Cell.Offset(1, 1).Value Then
        Range(Cells(Cell.Row, Cell.Column), Cells(Cell.Row, 2)).Copy Range(Cells(Cells(Rows.Count, 6).End(xlUp).Row + 1, 6), _
        Cells(Cells(Rows.Count, 6).End(xlUp).Row + 1, 7))
    End If
Next
Application.CutCopyMode = False
End Sub
ZAX
 
Upvote 0
A possible solution with formulas


A
B
C
D
E
F
1
Fruits​
Value​
Unique​
Value​
2
apple​
a​
apple​
a​
3
apple​
a​
pear​
a​
4
apple​
a​
orange​
b​
5
pear​
a​
grape​
a​
6
pear​
a​
banana​
b​
7
orange​
b​
apple​
b​
8
grape​
a​
orange​
a​
9
grape​
a​
10
banana​
b​
11
apple​
b​
12
apple​
b​
13
orange​
a​

Array formula in E2 copied down
=IFERROR(INDEX($A:$A,SMALL(IF(FREQUENCY(MATCH($A$2:$A$13&$B$2:$B$13,$A$2:$A$13&$B$2:$B$13,0),ROW($A$2:$A$13)-ROW($A$2)+1),ROW($A$2:$A$13)),ROWS($E$2:E2))),"")

Ctrl+Shift+Enter

Array formula in F2 copied down
=IF(E2<>"",INDEX($B:$B,SMALL(IF($A$2:$A$13=E2,IF(MATCH($A$2:$A$13&$B$2:$B$13,$A$2:$A$13&$B$2:$B$13,0)=ROW($A$2:$A$13)-ROW($A$2)+1,ROW($A$2:$A$13))),COUNTIF($E$2:E2,E2))),"")

Ctrl+Shift+Enter

Hope this helps

M.
 
Upvote 0
Thanks Hiker95, It's close but something didn't work correct, I have 10,000 rows and I know I only have 2000 unique parts. After running your macro using the advance filter, I ended up with a little over 5000. It looks like advance filter was getting hung up on the second column but in reality the second column is only along for the ride of the first column.
 
Upvote 0
Another option using menu option Remove Duplicates if you are using Excel 2007 or later.
Copy data from Columns 1& 2 to columns 6&7.

Select the range in columns 6&7 under DATA on ribbon Go To Remove duplicates (make sure you have selected the right range as this option will delete the duplicates in the range).
 
Upvote 0
Thanks Hiker95, It's close but something didn't work correct, I have 10,000 rows and I know I only have 2000 unique parts. After running your macro using the advance filter, I ended up with a little over 5000. It looks like advance filter was getting hung up on the second column but in reality the second column is only along for the ride of the first column.

Did you try my code? I just wanna know if it at least helps a bit :-/
ZAX
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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