Can a marco be created to solve my problem?

[lwilt]

I have an excel spreadsheet where I have duplicate email accounts with customer data. I'm trying to combine the customer data at each email account so that I can remove the dups and consolidate the customer data.

Currently I copy the emails over to a 2nd sheet and de dup. Bring over the headers for customer data. then use the formula:

IF(COUNTIFS(Sheet1!$A$2:$A$_,Sheet2!$A2,Sheet1!C$2:C$_,”Y”)>0,”Y”,””)

The problem I'm running into is that b/c my sheet is so big, ~190,000 records, that I get errors trying to copy this formula down. Is there a better way to do this with a Macro? please help!

 

[Luke M]

Let XL do the work for you. On your first sheet, go here:


Tell it which column potentially has the duplicates, and XL will automatically consolidate it for you.

 

[lwilt]

I did de dup it already....each email has unique customer data to it and I'm trying to combine all of that data at the email level so that are no more dup emails in the sheet.

So I de duped it in sheet 1 and in sheet 2 is where I used the countifs so to combine the customer data at the email level

 

[Marc L]

Following its last post, he created a new thread on another forum
just few minutes after …


​

 

[lwilt]

Is that against the rules to search out your answer in multiple places???

 

[Marc L]

Without noticing it and a link, yes ! It's called a wild cross posting : read this !

So I just warned Luke and anyone else to not waste their time
as you continue on another forum …

In the other forum, when a thread appears to be a cross post,
a moderator could lock it ! Wanna try next time ?
​

 

[lwilt]

wanna try what?

 

[lwilt]

so aren't you being a hypocrite saying people shouldn't waste their time answering questions to a user who visits multiple forums when you yourself are doing the same thing otherwise you wouldn't know if I posted anywhere else...interesting

thanks for all your help buddy

 

[Luke M]

Hi lwilt,

There's no harm in searching for an answer in different locations. We only ask that you provide a link to the other sites that you've posted on so that those you would potentially answer can see what has already been tried and/or if an answer has been found. It's quite frustrating to work hard on a problem for someone, only to discover that they found a solution hours ago.

Many people who like to answer forums (like myself), are members of different forums, which again is not the issue. If people tend to be perpetual cross-posters, moderators will often shut down the duplicate threads and/or warn the member. I believe this is what Marc was referring to with "Wanna try next time?"

On a side note, English is not Marc's native language, so sometimes it comes off different than he really means.

-------
Back to your question, could you provide a link to your other post, and/or provide a sample of what your workbook looks like now and what it should look like after 'duplicates' are removed?

 

[Marc L]

Its thread is yet solved on other forum …

Of course Iwilt should not write its last Dumb post if he read my link
explaining why a cross post is a bad habit !

And to conclude : Iwilt, visiting is not asking ‼
​

 

[lwilt]

I try to go back and post something if the problem has been solved where people were asking me questions and trying to help with the problem

here is the solution I was searching for that I've been testing all morning to make sure there aren't any holes and to cause any changes

Option Explicit

Sub test()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim LR1 As Long
Dim LC1 As Long
Dim i As Long
Dim j As Long


Application.ScreenUpdating = False
If Not Evaluate("ISREF(Output!A1)") Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Output"
Else
Sheets("Output").Cells.Clear
End If
Set ws = Sheets("Input")
Set ws1 = Sheets("Output")

ws.Cells.Copy
ws1.Range("A1").PasteSpecial
Application.CutCopyMode = False

With ws1
.Activate
LR1 = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
.Columns("B:C").EntireColumn.Delete
LC1 = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
For i = LR1 To 2 Step -1
If .Range("A" & i - 1).Value = .Range("A" & i).Value Then
For j = 2 To LC1
If Not .Cells(i, j).Value = "" Then
.Cells(i - 1, j).Value = .Cells(i, j).Value
End If
Next j
.Cells(i, j).EntireRow.Delete
End If
Next i

End With
Application.ScreenUpdating = True
End Sub

 

[lwilt]

I did ask questions...not sure what your talking about buddy