Free Microsoft Excel 2013 Quick Reference

VBA Code for Duplicate Names in One Row

I am trying to write a program that will find duplicate names in each row of an excel spreadsheet. I figured out how to do it for one row but can't figure out how to put it in a loop to check each row. Any help is appreciated. Thanks!!

Post your answer or comment

comments powered by Disqus
Hi can any one help me out to find a VBA Code for fliping images in a User Form. What I want is that, I have a user form in which I have a command button "Next" & "Previous". I want whenever I click "Next" button my image control should show the next image & whenever I click "Previous" button my image control should show the previous image. I tried many logics but failed. Might be you guys will crack this hard nut. Please post your comments if you need any further details from my side.I will appreciate your help.

Can anyone tell me how to use VBA code for the dynamic chart? I have 30 charts need to be plotted as dynamic chart. I know how to do it manually but it is very time consuming. However, there is always an error message for defining the name range in VBA.

I want to have a vba code for the cut range a2:d15 from sheet 1 and paste it to range a2:a15 in sheet 2. This command will be triggered by a command button.

Best Regards

I have a report with full names in one row and I'm trying to seperate them into 2 rows: First name & Last Name.

A curveball I've encountered is that some names have a middle initial but that can be discarded.

*BCDEF2ExFull Name*FirstLast31Michael Johnson*MichaelJohnson42Jane H Doe*JaneDoe

Excel tables to the web >> Excel Jeanie HTML 4

Thanks for the help!

Definitelly, nice to have met a fellow Excel fanatic!


David Billigmeier

"Manish Bajpai" wrote:

> David,
> You are simply great !!
> Nice to brainstorm with you.
> Thanks,
> Manish
> "David Billigmeier" wrote:
> > Sorry... I see a typo, paste that code in B1 :-)
> >
> >
> > --
> > David Billigmeier
> >
> >
> > "David Billigmeier" wrote:
> >
> > > Without using pivot tables:
> > >
> > > Assume your values are in the range A1:A10, paste this code in A1 and drag
> > > down:
> > >
> > > =IF(COUNTIF($A$1:$A$10,A1)>1,"dup","nodup")
> > >
> > > --
> > > David Billigmeier
> > >
> > >
> > > "Krista" wrote:
> > >
> > > > I need to check for duplicate names in a column. Is there a way to do this?

hi there,

need some help guys on getting a macro or vba code for copying data from one worksheet to another. on the attached file, i have mentioned on dark grey filled with bold text the constant parameters i need to copy or transfer every day on the dBase sheet next to it. can you please help me setting up a code that will work for this situation? i find it really hard to construct a vba code that will pull up the date, description, vessel type, etc. etc and paste it on the next sheet.i would like to have it on a single button that after clicking it auto populates the data i need on the other sheet on the corresponding headings. also, i want to protect the data into it to make sure that nobody can mess around with it . would appreciate any assistance. thank you very much for helping out.

Dear experts,
I wrote a VBA code for my friend in Excel 2002, but it would stumble in his 2003. Instead of installing the version and digging into the macro, I was hoping there would be an easy workaround. Therefore, although I suspect that this problem may be a result of something else, I wanted to clarify for me the following:

Are there any differences between VBA codes for Excel 2002 and Excel 2003, and if true, are there general guidelines with respect to the differences ?
Does the service pack status matter for Excel 2002? In other words would the same macro work on Excel 2002 regardless of whether it was updated or not?Thanks a lot in advance.

I need to check for duplicate names in a column. Is there a way to do this?

Hi guys

I'm old school learning and need some help with this report where I have one long column of costumers names ( last name, first name together in one A column ), many of them are duplicate in continuos rows when alphabetic sorted. I'll like know if a code can colors those duplicated names ( for instance 2 colors and the rows.
Thanks a bunch



I created this spreadsheet a few montsh ago to help with forecasting for my companies products.

The tabs of interest are the HIST vs FORECAST, FORECAST TOOLKIT, and the FORECAST TABLE tab – now the FORECAST TABLE tab is always hidden and if you unhide it then it will rehide itself once one of the macro’s is run. I set it that way to prevent people accidently making adjustments to the forecast data.

The way the sheet is supposed to run is that when you are on the hist vs forecast page then you can click one of the grey buttons on the right which will then graph the numbers for the relevant product on the toolkit page – so far so good, this bit all works fine, there are separate macro’s for this each named after the product they control.

Then once you are on the toolkit you can play about with the numbers until you come up with a 6 month forecast you are happy with which will be on the 6 cell line in the bottom right were it says “IMPORT”. You can then click on the “IMPORT” button which will then copy and paste it back into the forecast table under the relevant setting – the macro for this is called IMPORT2 and it works off a “product” range and a “date” range – again this pretty much works as intended.

    Dim nDate, nProd 
    With Sheets("Forecast Toolkit") 
        nDate = Application.Match(.Range("O31"), Range("Dates")) + Range("Dates")(1).Row - 1 
        nProd = Application.Match(.Range("I3"), Range("Products")) + Range("Dates")(1).Column 
        Sheets("Forecast Table").Cells(nDate, nProd).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
OK, here is the problem. We have recently added a new line of products called “ALBUNORM”, ive set it up just as I have with any other product but there is an issue that only affects these ALBUNORM products on the forecast toolkit tab. When you click IMPORT on any other product then the sheet works as intended and updates the FORECAST TABLE, but when you click IMPORT On any of the ALBUNORM products then instead of copying the data into the relevant product on the FORECAST TABLE, it instead copies it into the first column of the forecast table which is another product called ALBUMINATIV. All the ALBUNORM products were only added to the sheet this week but I have updated the named ranges to include them so cant really understand why its not working.

Can anyone help with why the VBA code for this import is not working only on these new products ?

Aloha, I have a new project that needs macro code. Your help is very much appreciated. We have a spreadsheet with duplicate accounts meaning two or three rows with the same account but different information. We want to use only one row for one account and move the new data from the same account to one row only to the right and delete the duplicates. Can someone please help me with this?I read so many post and I tried some of them but it only delete the duplicate row and not copying the new data from that row to one row only. Also, the other code I tried was retaining only the current or old data. Actually, to elaborate more, I want to get the new data in each cell of the same account in multiple row and move it in one row to the right only and delete the duplicate in that same account. Is this possible to do? Please help. Thanks.

Hi, all -

I'm posting this because I am doing an audit for work that is due (yesterday) and hoping someone can help me. I nneed to search for a name but return with the value in the next corresponding column. Can anyone help?

Here's an example.


Name Amount
Doe, John (***)

Doe, John
Doe, John
Doe, John 45
Smith, Jane
Smith, Jane 5
Smith, Jane

Sheet One - ***: This is where I would need the returned value. I want to search among multiple John Doe's in column A, but return with the value in column b. Only one value will be in column b per name, even though there may be multiples of John Doe in column A.

If I need to, I can go through and delete the duplicate names, so that I only search sheet 2 for the only John Doe and return with the value in that row.

Here's what I've tried: =VLOOKUP(A2,Sheet2!A$1:B$1140, 2, FALSE)

Obviously, it doesn't work or I wouldn't be posting on this forum.

Can anyone please help?

Hello All,

The below is the code i use for a specific requirement. I have some 7 files (Workbooks with different names) in the directory as i have mentioned in the code. It may increase too. But each file has the same no of sheets and of same format with the data different. I copy and paste the data from each sheet in another workbook for reporting. Data from first file will be arranged in one row and when its done it goes to the next file it should paste the data in the next row and so on. the First Row would be predefined column names. Could you please help me out in Iterations which could make it easy and which would be the best. Please edit the code and paste it for me.

Thanks for your help in advance.

Sub merched() 
    Dim SrcBook As Workbook 
    Dim fso As Object, f As Object, ff As Object, f1 As Object 
    Application.ScreenUpdating = False 
    Set fso = CreateObject("Scripting.FileSystemObject") 
    Set f = fso.Getfolder("C:RinaldoProject_Alexandre2011 excel report") 
    Set ff = f.Files 
    For Each f1 In ff 
        Set SrcBook = Workbooks.Open(f1) 
        ThisWorkbook.Worksheets(1).Range("A2") = SrcBook.Sheets("Synthèse Audit").Range("I1").Value 
        ThisWorkbook.Worksheets(1).Range("B2") = SrcBook.Sheets("Synthèse Audit").Range("C9").Value 
        ThisWorkbook.Worksheets(1).Range("C2") = SrcBook.Sheets("Synthèse Audit").Range("C7").Value 
        ThisWorkbook.Worksheets(1).Range("D2") = SrcBook.Sheets("Synthèse Audit").Range("C10").Value 
        ThisWorkbook.Worksheets(1).Range("E2") = SrcBook.Sheets("Synthèse Audit").Range("F8").Value 
        ThisWorkbook.Worksheets(1).Range("F2") = SrcBook.Sheets("Synthèse Audit").Range("F9").Value 
        ThisWorkbook.Worksheets(1).Range("G2") = SrcBook.Sheets("Synthèse Audit").Range("F7").Value 
        ThisWorkbook.Worksheets(1).Range("H2") = SrcBook.Sheets("Couverture Audits").Range("AA74").Value 
        ThisWorkbook.Worksheets(1).Range("I2") = SrcBook.Sheets("Synthèse Audit").Range("B43").Value 
        ThisWorkbook.Worksheets(1).Range("J2") = SrcBook.Sheets("Couverture Audits").Range("AB74").Value 
        ThisWorkbook.Worksheets(1).Range("K2") = SrcBook.Sheets("Synthèse Audit").Range("C43").Value 
        ThisWorkbook.Worksheets(1).Range("L2") = SrcBook.Sheets("Couverture Audits").Range("AA8").Value 
        ThisWorkbook.Worksheets(1).Range("M2") = SrcBook.Sheets("Couverture Audits").Range("AA17").Value 
        ThisWorkbook.Worksheets(1).Range("N2") = SrcBook.Sheets("Couverture Audits").Range("AA28").Value 
        ThisWorkbook.Worksheets(1).Range("O2") = SrcBook.Sheets("Couverture Audits").Range("AA45").Value 
        ThisWorkbook.Worksheets(1).Range("P2") = SrcBook.Sheets("Couverture Audits").Range("AA50").Value 
        ThisWorkbook.Worksheets(1).Range("Q2") = SrcBook.Sheets("Couverture Audits").Range("AA55").Value 
        ThisWorkbook.Worksheets(1).Range("R2") = SrcBook.Sheets("Couverture Audits").Range("AA71").Value 
        ThisWorkbook.Worksheets(1).Range("S2") = SrcBook.Sheets("Couverture Audits").Range("C38").Value 
        ThisWorkbook.Worksheets(1).Range("T2") = SrcBook.Sheets("Couverture Audits").Range("D38").Value 
        ThisWorkbook.Worksheets(1).Range("U2") = SrcBook.Sheets("Couverture Audits").Range("E38").Value 
        ThisWorkbook.Worksheets(1).Range("V2") = SrcBook.Sheets("Couverture Audits").Range("F38").Value 
        ThisWorkbook.Worksheets(1).Range("W2") = SrcBook.Sheets("Couverture Audits").Range("C39").Value 
        ThisWorkbook.Worksheets(1).Range("X2") = SrcBook.Sheets("Couverture Audits").Range("D39").Value 
        ThisWorkbook.Worksheets(1).Range("Y2") = SrcBook.Sheets("Couverture Audits").Range("E39").Value 
        ThisWorkbook.Worksheets(1).Range("Z2") = SrcBook.Sheets("Couverture Audits").Range("F39").Value 
        ThisWorkbook.Worksheets(1).Range("AA2") = SrcBook.Sheets("Couverture Audits").Range("C40").Value 
        ThisWorkbook.Worksheets(1).Range("AB2") = SrcBook.Sheets("Couverture Audits").Range("D40").Value 
        ThisWorkbook.Worksheets(1).Range("AC2") = SrcBook.Sheets("Couverture Audits").Range("E40").Value 
        ThisWorkbook.Worksheets(1).Range("AD2") = SrcBook.Sheets("Couverture Audits").Range("F40").Value 
        Application.CutCopyMode = False 
        SrcBook.Close SaveChanges:=False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


How do you use VBA CODE for ranges in graphs?

Sub ChartCreate()


lr = ActiveSheet.Range("C2").End(xlDown).Row

'ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
'        "Sheet2!R1C1:R" & lr & "C3").CreatePivotTable TableDestination:="", TableName:= _
'        "PivotTable1"

Columns("C:C").NumberFormat = "m/d/yyyy"

    Set rg = Range([C2], [C2].End(xlDown).End(xlToRight))
    'Range([C2], [C2].End(xlDown).End(xlToRight)).Select
    ActiveChart.ChartType = xlColumnClustered
  **  ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("C2:D11"), PlotBy _
In the line marked **

I want to substitute range "c2:d11" with range ( rg ) shown above but get an error when I try this. Otherwise, I have to manually type in the range everytime I create a graph.
Please could someone help me with this.

I do this quite a lot by hand, and was wondering if it could be automated using VBA ?

Ok, imagine columns A:I, with the number of rows being different from one job to the next... but it always has 9 columns in all, column A never has any breaks in it, so maybe that could be used to find last row used ?

The columns that I work on are G and H, and they contain:

Column | Values it contains

G - has either a Y or N.
H - has ID numbers like (213123123), or blanks

I turn autofilters on column H, and select all the blanks (I recorded the VBA code for this using the Macro recorder), so far the VBA code works up to this point. Now that I have selected all the blanks, I want to insert the words "Not Available" into all the selected blanks in column H, this is where I am stuck as I am not sure how to do that.

Once I do that manually, then I select "All" in the autofilter for column H to reset it, and to see all the records back again in column H, and then I move to column G and select from the autofilter N, and then I go back to column H and here there will be a combination of ID numbers (123123123) and cells with the words "Not Available" displaying, from the previous step.

I need to have the words "Not Available" for all the records selected in column H, when I select N from column G. I hope I am not confusing anyone

Can this be done ?

Anyw help would be appreciated, thank you.

I am running a macro to check statuses and currently I have created the following code to check if the previous rows have also failed. I am wondering if there is an easier way to represent this. My code is below:

Sub test

Dim xRow As Integer 'Used to indicate row on report spreadsheet
Dim wks2 As Worksheet 'Short for worksheet name, "Pivot" in workbook "Reporting.xls"
Set wks2 = Workbooks(Reporting).Sheets("Pivot")
For xRow = 5 To wks2.UsedRange.Rows.Count
If Trim(wks2.Cells(xRow, 7)) = "Failed" And Trim(wks2.Cells((xRow - 1), 7)) = "Failed" Then
wks2.Cells(xRow, 9) = "Second Failure"
End If
If Trim(wks2.Cells(xRow, 7)) = "Failed" And Trim(wks2.Cells((xRow - 1), 9)) = "Second Failure" Then
wks2.Cells(xRow, 9) = "Third Day of Failures"
End If
If Trim(wks2.Cells(xRow, 7)) = "Failed" And Trim(wks2.Cells((xRow - 1), 9)) = "Third Day of Failures" Then
wks2.Cells(xRow, 9) = "Fourth Day of Failures"
End If
If Trim(wks2.Cells(xRow, 7)) = "Failed" And Trim(wks2.Cells((xRow - 1), 9)) = "Fourth Day of Failures" Then
wks2.Cells(xRow, 9) = "Fifth Day of Failures"
End If
If Trim(wks2.Cells(xRow, 7)) = "Failed" And Trim(wks2.Cells((xRow - 1), 9)) = "Fifth Day of Failures" Then
wks2.Cells(xRow, 9) = "Multiple Failures"
End If
Next xRow

End Sub

Hello group! In a earlier post I had sought help trying to delete any duplicate info if there was a duplicate number in column V and have it delete the last entry row. With help from this site I was able to get that to work. The problem that I've since discovered is that I need to really look at the duplicate numbers to see what row of the duplicates that I would prefer to keep before deleting any of them; due to content entry if slightly different, etc. Thus my new request for help!

What I'd like to do is have help with the following code or new code if possible which will allow me to identify the duplicate numbers in column "V" and have them appear in a message window. The code would search the entire column of data and afterwards pop up a message window that would say something to the effect "The following complaint #'s have duplicate entries: 454,462,527,..." etc.
Then I would be able to manually filter those numbers and delete the row(s) I choose.

The following code currently identifies the number of duplicate entries in column "V" and deletes the 2nd entry in Column "V" only (not the entire row). Afterwards a message window will appear notifying me something to the effect "There were 10 duplicate entries deleted". Would anyone be able to help modify this code to just identify the duplicate entry numbers instead of deleting anything?
Any help would be appreciated.

Sub DeleteDuplicateEntries()
   Dim rClMain As Range
   Dim rClDupe As Range
   Dim rCheck
   Dim N As Long
   Dim LR As Long, i As Long, found As Range
   Application.ScreenUpdating = False
   N = 0
       'currently starts in A6 down,adjust to your data
   Set rCheck = Range(Cells(6, 22), Cells(Rows.Count, 22).End(xlUp))
   For Each rClMain In rCheck
        '1st loop - (to speed things up ignore any empty cells)
       If rClMain <> Empty Then
           For Each rClDupe In rCheck
                '2nd loop - compare non-empty rClDupe values
                'and clear contents if it's a duplicated value
               If rClDupe <> Empty And _
               rClDupe.Value = rClMain.Value And _
               rClDupe.address <> rClMain.address Then
                   N = N + 1
               End If
           Next rClDupe
       End If
   Application.ScreenUpdating = True
   MsgBox "There were " & N & " duplicated entries deleted"
End Sub

Dear All,

I have managed to sort it out and it is all working fine now. Thanks very much.

I need help to fine-tune my Excel UserForm VBA code for the ‘Add Data’ button

My UserForm has the following buttons now:

b) A 'Close Form' button. When this is clicked, the form is closed after entering and adding data to the database.

c) A 'Add Data' button. When data is entered on the UserForm, upon clicking this button, the database is updated with the data.

However, if by error, someone clicks the 'Add Data' button without any data having been entered on the UserForm, a debug screen comes up and the person is taken to the VBA editor and there the following code lines are highlighted in yellow colour:

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

Please is there any line I could add to the 'Add Data' code so that when the 'Add Data' button is clicked without any data entered on the UserForm, nothing should happen. Rather a message should come up asking the user to 'Add data to the UserForm'.

The current code I have for the 'Add Data' button is as follows:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets ("Payments")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row Thanks for any help.


Hi. I need a way (can be a macro) to find duplicate words in a column, and then to copy those duplicated values to another column.

An example. My column looks like this:

Row1 - John Cage / Johnny Bravo / Rambo / Indiana Jones / Neo
Row2 - Frodo / Bruce Lee / Steve / Harry / John Cage

I need to find every single duplicate name, and then to copy them to another column. Problem is, I can't seem to find a macro that does that - since the macros I have look only for exact values in the rows, they don't recognize as duplicate anything in a row that simply has a partial identification with another row. For example, the macro I have does not recognize as duplicate the "John Cage"s in those two rows - because their respective rows aren't exactly equal, only two words in them are - the "John Cage"s. But I need to find those "John Cage"s.

Is there a solution for my problem?
Thank you for any advice you may be able to offer.


I am a newbie here. Can I ask your help. I need a VBA CODE to combine these in one without the numbers in red. It will be very helpful bec, I need to do more than 36 like this.
A1.Order #38588 - Advance Payment for 01/11/2011 to 30/11/2011 1250 £2.50 £3,125.00 £0.00 A2.Tenant Loan over £5,000, excluding car leads A3.Ordered: 2500

Order #38588 - Advance Payment for 01/11/2011 to 30/11/2011 Tenant Loan over £5,000, excluding car leads Ordered: 2500

Order #38588 - Advance Payment for 01/11/2011 to 30/11/2011 1250 £2.50 £3,125.00 £0.00 Tenant Loan over £5,000, excluding car leads Ordered: 2500

Order #38588 - Advance Payment for 01/11/2011 to 30/11/2011 125 Homeowner Loan £5,000 to £9,999, over 80% LTV leads Ordered: 250

We have a worksheet entitled 'Data Output' that has the raw data in column B. The data represents temporary input from another worksheet that serves as an intake form. Each person's intake data will temporarily populate column B of the Data Output worksheet. When each intake is complete, I want Excel to take the data in Column B and find the next available empty column in a worksheet called 'database page 1' and populate it with the data starting at row 3 downward. When the last column in database page 1 worksheet is reached, I need for Excel to go to 'database page 2' worksheet and begin populating the next empty column and so on.

Is there anyone within your forum or group that can help me write the VBA code for this and briefly tell me where in Excel to insert the code. I know formulas basically well, but have never used VBA.

Thanks in advance for your assistance.

Russell Rigsby

I have a list of about 1000 names with 2 columns next to it. The first contains a list of different groups that the names belong to (there are 19 possible groups). The second contains a list of figures ranging between £10,000 and £500,000.

I want a formula that will look for a name in the list and, when it finds the name, return the corresponding group and corresponding amount.

My problem is that some names are in more than one group and therefore appear in column 1 more than once. I have tried to use the LOOKUP formula [eg looking for the GROUP =LOOKUP(Sheet1!B33, Sheet2!A:A, Sheet2!B:B)] but when a name appears more than once I cant get all the results - for example:

Mr Phil Little Green Group 35,000
Mr Ed Grant Red Group 20,000
Mr Robert Smith Orange Group 10,000
Mr Robert Smith Red Group 50,000
Mr Robert Smith Green Group 75,000
Mr James Morrison Green Group 100,000

On my summary page (Sheet1) I would like to type in Mr Robert Smith (into cell Sheet1!B33) and in the rows below (obviously in 9 different cells) have the results appear but I can only ever get Mr Robert Smith Green Group 75,000 as a result.

This is my first post so I hope I have obeyed all the rules and this is clear, some help would be HUGELY appreciated.



Hi all

I need a VBA code for a summation formula. I have a column of values which are calculated by a formula.But the value of the second cell below the 1st cell in the column is a summation of the calculated value in the 1st + 2nd cell of the same column and the 3rd cell below the top is a summation of the 3 calculated values and subsequently for a column of 10 rows.
Each cell in the column uses the same formula with input according to the row in ascending order.

Can any experts out there pls give me some help.Urgent. Thx.

Hi friends,

I have the formula below, it is in column BR. It goes from column V to BN and it should be copied down from row3 to Row 700. The problem I am having is the formula is too long for the cell, what would be the quivalent VBA code for this?

Thanks for any assistance you can provide.

=IF(V3="X",V$1,”“ )&” “&iF(W3="x",W$1,““)&” “&IF(X3="x",X$1 ,”“)&” “&IF(Y3="x",Y$1 ,”“)&” “&IF(Z3="x",Z$1&” “&” ‘,””)&” “&IF(AA3="x",AA$1 ,”“)&” “&IF(AB3="x",AB$1 ,”“)&” “&IF(AC3="x",AC$1 ,”“)&” “&IF(AD3="x",AD$1 ,”“)&” “&IF(AE3="x",AE$1 ,”“)&” “&IF(AF3="x",AF$1 ,”“)&” “&IF(AG3="x",AG$1 ,”“)&” “&IF(AH3="x",AH$1 ,”“)&” “&IF(AI3="x",AI$1 ,”“)&” “&IF(AJ3="x",AJ$1 ,”“)

The sheet looks like this:

v w BR
1Criteria1 Criteria 2
2 x =if(V2="x",V$1,"")&" "&if(W2="x",W$1,"")
3 x x =if(V3="x",V$1,"")&" "&if(W3="x",W$1,"")

No luck finding an answer? You could always try Google.