Match consolidate multiple rows data into one row

F

francis

Hi

How do I achieve this in formula,


Customer Address E_mail
ABC Cone 700 W.ST 2RD [email protected]
ABC Cone 700 W.ST 2RD [email protected]
ACME 701 ACME DR [email protected]
SANDALWOOD 200 SANDALWOOD AVE [email protected]
SANDALWOOD 200 SANDALWOOD AVE
SANDALWOOD 200 SANDALWOOD AVE [email protected]
DIGITAL TIMES 919 N MAIN ST [email protected]
LUXURY SUITES 6616 DAVIS BLVD
[email protected]
LUXURY SUITES 808 E MAIN [email protected]
LUXURY SUITES 6616 DAVIS BLVD [email protected]
MISHMASH 350 MISHMASH PARKWAY
[email protected]
XYZ FOUNDATIONS 502 WEST LITTLE [email protected]
XYZ FOUNDATIONS 123 MAIN [email protected]
XYZ FOUNDATIONS 502 WEST LITTLE [email protected]
XYZ FOUNDATIONS 502 WEST LITTLE
XYZ FOUNDATIONS 502 WEST LITTLE [email protected]
XYZ FOUNDATIONS 555 SOUTH PENGUIN [email protected]


Expected Result

Customer Address E_mail
ABC Concrete 700 W.ST 2RD [email protected];[email protected]
ACME 701 ACME DR [email protected]
SANDALWOOD 200 SANDALWOOD AVE
[email protected];[email protected]
DIGITAL TIMES 919 N MAIN ST [email protected]
LUXURY SUITES 6616 DAVIS BLVD
[email protected];[email protected];[email protected]
LUXURY SUITES 808 E MAIN
[email protected];[email protected];[email protected]
MISHMASH 350 MISHMASH PARKWAY
[email protected]
XYZ FOUNDATIONS 502 WEST LITTLE
[email protected];[email protected]
XYZ FOUNDATIONS 123 MAIN
[email protected];[email protected]
XYZ FOUNDATIONS 555 SOUTH PENGUIN
[email protected];[email protected]

TIA


--


Thank You

cheers, francis
 
E

Eduardo

Hi Francis,
Try, change according to your needs this suppose that duplicates are in
column B starting to check in row 6

'delete duplicates

Dim TestColumn As String
Dim RowNdx As Long
Dim TopRow As Long
Dim LastRow As Long
Dim WS As Worksheet
Dim DeleteThese As Range

Worksheets("Summary").Select




Set WS = ActiveSheet
TestColumn = "B" '<<<< column to test for duplicates
TopRow = 6 '<<<< top-most row of data to test.

With WS
LastRow = .Cells(.Rows.Count, TestColumn).End(xlUp).Row
For RowNdx = LastRow To TopRow Step -1
If Application.CountIf(.Range(.Cells(TopRow, TestColumn), _
.Cells(RowNdx, TestColumn)), _
.Cells(RowNdx, TestColumn)) > 1 Then
If DeleteThese Is Nothing Then
Set DeleteThese = .Rows(RowNdx)
Else
Set DeleteThese = _
Application.Union(DeleteThese, .Rows(RowNdx))
End If
End If
Next RowNdx
End With
If Not DeleteThese Is Nothing Then
DeleteThese.Delete
End If


Sheets("Summary").Select

'This macro delete all rows with a blank cell in column B
On Error Resume Next 'In case there are no blank cells
Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0





End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(what:="*", _
after:=sh.Range("B1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
 
F

francis

Hi Eduardo

Thanks for the effort but the macro does nothing as the Summary page
is blank as a result after running the code.

Is there formulas that can do this?


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis
 
E

Eduardo

Hi Francis,
The summary page was my example, you have to replace that name for your tab
name
What the macro will do is to look into your tab and delete any duplication,
is that what you want to do or you want to copy your information in another
tab and then delete the duplicates
 
F

francis

Hi Eduardo

Thanks for the reply.
What I like to do is
1) to check column A with column B whether they are the same
2) if they are the same, then check column C if the Emails are the same
3) if the range in column A, B, and C are the same, I need only return the
same email, otherwise return all the email which are different
4) Col A may have the same name but the Address in col B may be different,
then I need to return col A, B and the email relating to Col B

Hope I have explained myself well. I apology for not able to explain well
enough
 
Top