Free Microsoft Excel 2013 Quick Reference

Find duplicate cells in excel Results

Automatic data extraction, calculating and copying results using condition (numerical and/or other) to another Excel workbook (containing not constant count of worksheets).

Basically what I want to do is to make as automatic as could be data extraction from alphanumeric strings (in ““source” ” workbook, raw data worksheet column “H” – added as an example), calculate final results based on several criteria in alphanumerical excel cells. [The row count in “source” is not constant – it may vary.]

And then automatically search in “result” workbook trough all containing worksheets and copy results from “source” to “ABN” column in “result” workbook worksheets (basing on reference in “source” – in this case Account No. column and the same reference in “result” workbook worksheets in column B).

How far I am:
1)found the way to extract needed numbers from alphanumerical strings in separate columns (basing on conditional and mid formulas);
2)made condition based calculations;
3)using vlookup, if and reference numbers copied results to “result” test workbook, but it’s all still more or less manual work;

Even this seemingly tiny advancement saved me 6 hours of work (for 1-2 “result” workbook worksheets, where I had to make manual calculations for every reference cell, and then manually write the result according to reference in “source” data reference.

Problems encountered (couldn’t deal with):
1)the reference numbers contain duplicate values in the same column (so vlookup takes only first encountered referenced result and applies to all duplicates in “result” workbook), but different results (which are needed in final worksheets), I was thinking about implementing second condition (basically possible, but couldn’t find the way to make vlookup use it) or add the rule that if no result found in first row all 3 columns then search in next row (in array of repeating references), then it would be possible to order results in different columns, but as I’ve no idea how should this rule look, so I was thinking maybe it’s possible to implement dropdown list for those duplicate reference results (to be able at least manually choose which result goes where – but as I’m not too skilled in excel couldn’t figure that out);
2) in “result” workbook repeating references are in the same worksheet (by meaning that reference numbers are specific to each worksheet);
3)it may happen that reference number in “source” workbook doesn’t contain according reference in “result” workbook worksheets, and/or reference number in “result” workbook worksheets doesn’t have according reference to “source” worksheet (happens frequently), in this case results (in ABN column) should be replaced with 0;

As I’m not familiar with VBA/Macros, but I have heard that with VBA you can do almost everything. So my idea is VBA, where you can choose file one as “source” , make all needed extractions and calculations (automatically), and choose file two where to put these results.

Maybe you have some idea, and could give me some helping hand with this one? Thanks in advance.

Couldn't add trough add attachments.

<a href=><img src= width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href= >file upload storage</a></div>

<a href=><img src= width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href= >upload files online</a></div>

Excel 2003 system.

Greetings sirs (and ladies?)

I think this belongs in the programming forum. I will be both apologetic and happy, though, if you can suggest a solution that does not require programming. If a programming solution IS required, I'd be grateful if you could give me a note or two on how to run the code if it is necessary. I'm competent with computers and I could program what I need in C++ if I had to, but I haven't used VBA before.

Here's my excel problem:

I have two long sets of data:

One is pressure from a transducer under water (in the river) recorded every 30 minutes. The other is pressure from a transducer above the water recording every hour.

I need to find the pressure due to water for each point (meaning I need to subtract the atmospheric pressure from each point of total pressure). From that, the height of water can be calculated, which will allow me to calculate discharge, or flow, of water at this spot in the river.

Because the atmospheric pressure is only recorded hourly, I need to duplicate each row of the atmospheric data worksheet so I can copy it over and make it the 'subtract' column.

Since I am working with years of data, there are thousands of rows, and the idea of duplicating each row manually is lame.

I tried to figure out a way for my calculation formula to use each row of the 'subtract' column twice (by making the first two subtract the value in E5, the second two use E6, the third pair use E7, and then dragging the auto-fill formula thingy down through the whole data set, but it doesn't work because the first one that gets auto-filled subtracts the value right next to it {..., D9-E7, D10-E7, D11-E11, D12-E11, ...} and so on).

So, like I said, I think i'll probably need to program it. If there was a way get the auto-formula-fill thingy to stop skipping back to the cell directly next to it as soon as it starts over the loop of copying, then that would be great.

Thank you for your help, and I apologize if this has been posted before, but all I could find were like a billion threads on deleting duplicate data.


I have created a spreadsheet that shows a list of mp3s on my PC and generates a playlist for those that I select 'yes' to uploading.

In order to make this easier for continual updates, I would like to be able to post raw data into one worksheet (taken from Access via the 'analyse with excel' function) and have the main sheet grab information from that. This means I won't have to keep adding formulas everytime I update my mp3 list.

'music' is the main worksheet I have set up
'data' will be the raw data taken from Access.

The format is as follows (starting in A1 with '>' indicating a new cell):

The above is the header row and the data follows on from that, anywhere from 10 to 5000 songs.

Using a command button I want 'music' to be able to grab the the information starting with the first artist (A2) and ending with the final filepath of the last row.

Basically, it means 'music' will copy all information in the range of the 'data' spreadsheet at the click of the button - and this will work regardless of how much information is in the 'data' sheet.

I can use =COUNTA(A:A) and the same for (1:1) to determine how many rows and columns should be in the range, but have no real idea what the code is for getting this infomation to show in the 'music' sheet.

I was playing around with it last night in an attempt to find out how things worked, and the best I could manage was to take information on the same sheet columns A-C and offset it by three rows (G1 was the COUNTA sum to determine how many rows had content).

Private Sub CommandButton1_Click()
Cells(1, 1).Select
For rownum = 1 To Range("G1").Value + 1
    For col = 1 To 3
        If Cells(ActiveCell.Row, col).Font.Bold = False Then
            Cells(ActiveCell.Row, Cells(ActiveCell.Row, col).Offset(0, 3).Column) = Cells(ActiveCell.Row, col)
        End If
Cells(rownum, 1).Select
End Sub
Although this helped me gain a little knowledge, it was then I realised I am out of my depth a little in trying to get it to work as described above.

Any pointers would be greatly appreciated - thanks.


Hi all.

I am new to Excel Macros.

Please help me with this macro.

I am trying bit by bit, but got stucked in deleting the rows.

I have gone through the forums and tried some solutions but it is not deleting all rows.

My requirement is like this.

11001706 Product Development & Finance Process
Test Scenario
JAN2010 FEB2010
(A4) '8110000000 247479.64 247482.64
1812 '8110000000
1600 '8110000000 246975.64 246975.64

I want to delete the rows for which the Coulmn A cells are empty for a entire sheet, but starting from A4 cell to the end A6.......

Also, if two or more rows have the same cells values in Column A and Coulmn B like 1204 '8766000000
as shown below the two records must be summed up for the particualr months and summed up value must be placed as a single record.

JAN2010 FEB2010 MAR2010 APR2010 MAY2010 JUN2010 JUL2010 AUG2010 SEP2010 OCT2010 NOV2010 DEC2010

1204 '8766000000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000
1204 '8766000000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000

Output must be as shown below:

JAN2010 FEB2010 MAR2010 APR2010 MAY2010 JUN2010 JUL2010 AUG2010 SEP2010 OCT2010 NOV2010 DEC2010
1204 '8766000000 2000 2000 2000 20000 2000
2000 2000 2000 2000 2000 2000 2000

I tried the below code but it is deleting the A1 and A2 rows also and it is not deleting all rows.

Please help me.

Thanks In Advance.

Please find the attachment for the file layout

I have the following code that copies data to another workbook, then saves
the file as the employee name in cell C2. The user can define the location
the file saves.

Private Sub CommandButton4_Click()
'rCell Makes a copy of the initial calculations and saves to the Data
'rFound looks for a duplicate date and if found copies over it else copies
to next avail row
Dim rCell As Range
Dim rFound As Range
With Application.ThisWorkbook
Set rFound =
..Worksheets("Data").Columns("B").Find(What:=(.Worksheets("STD Calc") _
.Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
..Worksheets("Data").Range("A65536").End(xlUp).Offset(1, 0)
Set rCell = rFound.Offset(-3, -1)
End If
Worksheets("STD Calc").Range("B17:P37").Copy
rCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
'the following opens the "Save As" MsgBox in Excel so the user
'can save to the location they desire. File is saved as name of employee in
cell C2
Dim RetVal As Variant
RetVal = Application.GetSaveAsFilename(Range("C2"))
If RetVal <> False Then
ThisWorkbook.SaveAs RetVal
End If

End Sub

The problem I have is after saving the file to the desired location the file
is saved as type "File" not .xls. I don't know what that means exactly.
However when the file is opened for a second time and changes made, then the
user clicks the Save command button which fires the above code, and I get a
runtime error saying Unable to locate file "employee name".

Why can't it open the file? If I close and go to the file and open it is
fine. I just can't replicate the code above a second time.

Thanks for your help!

I am sorry if this is a Duplicate post, i searched and could not find anything that was what would work for me.

This is my problem. I have two sheets, one has data in a table with column headings and the other is formula or calculation sheet. On the calculation sheet I have six sets of three columns each. The first five sets are basically the same, Ball #1, Number of Times Drawn, Date Last Drew. The only column that will change the column heading is the Ball #, it will go from 1 to 2 to 3 to 4 to 5. The sixth column has the same two columns, Number of Times Drawn and Date Last Drew. The first column in the sixth column set is labeled Mega Ball. (See Pics Attached Below)

Yes I am doing a stats analyses of lottery numbers and there drawings for a project.

This is my problem, I can't get the Date Last Drew to work. I have tried the following formula:

The original formula was slightly different, in the MAX() function and the *ROW() function, I had "Item=A2" in the () but I received a #Name error. I figured out that Excel didn't know what "Item" was so I changed it to just the cell reference, A2. The data sheet has a table named Table1, side note Excel doesn't see Table 1 it only sees Table8 through 14(??), with the column headings Date, Ball #1, Ball #2, Ball #3, Ball #4, Ball #5, Mega Ball, and Multiplier. So when the formula is referencing the range on the data sheet I am using "Drawing_Date[Date]" to specify the range of draw dates.

When I run the formula I get a date, that seems random and is clearly wrong, and I can not figure out where it is pulling it from. I have pasted in a couple of snap shots of the two sheets for you to see. I am not even sure if I am on the right track any more. I have read so much trying to figure this out that my brain seems to be turning to mush!!

Please note that I really want a VBA solution as I am in no way shape or form good with VBA. If I can figure out the formula/function for the first one I think I can adjust it for the rest. (I hope )

Thanks for all the help!!
Mike C

Calc Sheet Snap Shot

Data Sheet Snap Shot

I was wondering let say I'm entering like tons of digits in an cell column. Only in that column have all that digits, I was wondering if there anyway in excel to figure out if any of those numbers completely matched, meaning there an duplicate. Let say line 2 have 12345 and line 12 have 12345, thats completely matching and I want to find those with the least time consuming.

Hi Guys,

Im having trouble extracting data from multiple workbooks into one master workbook I’m working with excel 2000 and all the files in question are located in the same Folder on my desktop.

I can get the data to copy into the master as long as all workbooks are open. (Not what I’m after as it will be pulling in data from 20+ workbooks when the thing is finally up and running)

I found a hack on the net were by code opens each workbook one at a time and then closes is after it has done its thing.

I want to integrate my code with the code I found on the net… as follows

The two pieces of code….

Sub OpenAllWorkbooks2003()
'Open a With structure for the Application object and prepare Excel.
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.AskToUpdateLinks = False
'Declare and define variables
Dim SourcePath As String, iFile As Integer
SourcePath = "C:/Your/File/Path/"
'Open each workbook in the source folder, and do something with it.
With .FileSearch
.LookIn = SourcePath
.Filename = "*.xls"
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
For iFile = 1 To .FoundFiles.Count
Workbooks.Open (.FoundFiles(iFile))
'This is where your actual code would go to do whatever you have in mind with these workbooks.
MsgBox "Workbook that is open now:" & vbCrLf & ActiveWorkbook.Name & vbCrLf & vbCrLf & _
"Your code would go here to do something with these workbooks.", , "Example"
'Save and close the workbook and move on to the next one.
ActiveWorkbook.Close True
Next iFile
'Advise the user if no workbooks exist in the folder.
With Application
.AskToUpdateLinks = True
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
MsgBox "No such files exist in the path " & SourcePath & ".", , "Nothing to open."
Exit Sub
End If
End With
'Close the With structure for the Application object and reset Excel.
.AskToUpdateLinks = True
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
'Advise the user that the macro is complete
MsgBox "All workbooks in " & SourcePath & vbCrLf & _
"have been opened and closed.", 64, "OK, all done !!"
End Sub

And I need to somehow integrate this into my code which is…..

Sub update()

On Error Resume Next

For pm = 1 To 20
If Sheets("Project Managers").Cells(pm, 1).Value = "" Then Exit Sub

PM_NAME = Sheets("Project Managers").Cells(pm, 1).Value

xlscreenupdating = False
Workbooks.Open (PM_NAME & ".xls"), xlReadOnly

R_PM = 4: exit_do2 = False

EMD = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 2).Value
R_SM = 4: exit_do1 = False
If Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 2).Value = "" Then
exit_do2 = True
Exit Do
End If
If Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 3).Value <> "" _
And Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 2).Value = EMD And _
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 3).Value <> PM_NAME And _
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 2).Value <> "" Then
MsgBox ("Error - Duplicate EMD on line" & " " & R_SM & " on " & PM_NAME & ".xls")
Exit Sub
End If

If Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 2).Value = EMD Then 'find project in summary
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 4).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 4).Value 'if found then paste
Exit Do 'now exit for new
End If

If Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 2).Value = "" Then 'if cannot find then new project
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 2).Value = EMD 'Project ref
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 1).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 1).Value 'project title
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 3).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 3).Value 'project Officer
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 4).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 13).Value 'Project Budget
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 5).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 14).Value 'contractor
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 6).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 15).Value 'Tender Value
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 13).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 4).Value 'First date
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 14).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 5).Value
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 15).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 6).Value
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 16).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 7).Value
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 17).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 8).Value
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 18).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 9).Value
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 19).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 10).Value
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 20).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 11).Value
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 21).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 12).Value
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 22).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 13).Value
Workbooks("EMD Project Register v1.2b 2010-11.xls").Sheets(2).Cells(R_SM, 23).Value = Workbooks(PM_NAME & ".xls").Sheets(1).Cells(R_PM, 16).Value

Exit Do
End If
R_SM = R_SM + 1
Loop Until exit_do1 = True Or R_SM = 1000 'exit

R_PM = R_PM + 1

Loop Until exit_do2 = True
'Workbooks(PM_NAME & ".xls").Close
xlscreenupdating = True

End Sub

Or if someone can think of an easier way to do this I would be forever in your debt, ha
Thanks and regards


Sorry if the answer is here somewhere i can't seem to find it..

Ok i have a list of unique values in column J.

I would like to check column B and if any of these values match values in column J hightlight the cell on the same row as the match on column E green. I don't know much about excel but this would be a tremendous help because i'm looking through quite a bit of data. I've tried using conditional formatting and i can't seem to get that to work for my needs. Let me know if you need anymore information. Thanks in advance!

I've trawled the fora but can't find a similar thread, so am posting this - apologies if it's a duplicate!

In Excel 2007 I want to apply multiple conditional formats to cellse.g.

If A1 = "A" then <fill cells A2:A12 with red colour>
If A1 = "B" then <fill cells A2:A12 with green colour>
If A1 = "C" then <fill cells A2:A12 with blue colour>

This was easy in Excel 2003 by simply adding a format (to a maximum of 3 formats), but I just can't fathom how to do it in Excel 2007 - all help gratefully received!


I'm using Excel 2003 (11.8117.8107) SP2.
There are two spreadsheets in the attached workbook.
There are no macros in the attached workbook.

The user names shown in column A of the “Vendor Data” spreadsheet are the same user names shown in the name column (column A) of the “User Data” spreadsheet. There are no duplicate rows in “User Data” but there are duplicate rows in the “Vendor Data” spreadsheet.

What I need is a process to accomplish the following:
For each name in column A in the “Vendor Data” spreadsheet:
1. Find that name in column A of the “User Data” spreadsheet and change that entire row to bold to indicate that this name was found and processed.
2. Copy the cell contents of columns B&C in the “User Data” spreadsheet to columns D&E in the “Vendor Data” spreadsheet.

Any help you can provide is appreciated.

Jack Gombola

Hi all

I have managed to use macros provided in to get my web query to work correctly. The only problem I have is the archive page duplicates previous data. I have found an easy solution for this in the data ribbon, using remove duplicates.

I have set up a macro to remove duplicates for me, which works fine, but I was wondering is there any way of triggering the macro each time the web query refreshes, so I dont have to manually run the macro every time. I have tried seaching the web but cannot find the solution I am looking for.

The code used for the web query to transfer to the archive tab is as follows:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, MyRNG As Range, DateFIND As Range

On Error Resume Next
Set MyRNG = Range("A:A").SpecialCells(xlConstants, xlNumbers)
If MyRNG Is Nothing Then Exit Sub

With Sheets("Archive")
    For Each cell In MyRNG
        Set DateFIND = .Range("A:A").Find(Format(cell, cell.NumberFormat), LookIn:=xlValues, LookAt:=xlWhole)
        If DateFIND Is Nothing Then
            cell.Resize(, 16).Copy .Range("A" & .Rows.Count).End(xlUp).Offset(1)
            Set DateFIND = Nothing
        End If
    Next cell
    .Range("A:s").Sort Key1:=.Range("b3"), Order1:=xlAscending, Header:=xlYes
End With

End Sub

and the duplicate macro is as follows:

Sub duplicateRemove()
' duplicateRemove Macro

    ActiveSheet.Range("$A$1:$Q$10000").RemoveDuplicates Columns:=Array(2, 3, 4, 5, 6, 7, 8 _
        , 9, 10, 11, 12, 13, 14, 15, 16, 17), Header:=xlYes
    ActiveWindow.SmallScroll Down:=18
End Sub
Thanks in advance

I am having some difficulties with a formula that is beyond my current experience level to create. My template is to create a way to run our reports against each other and label in Column A which report should have the responsibility of the ID numbers on the reports.

My end goal for the formula is this:
1. If Column C="PDD", then Column A should = "PDD"
2. If Column E= "BF", "IA" or "RI", then Column A should= Column E
3. If Column C= blank, then Column A should= Column E
4. If Column C= "SDD" and Column B<>"Y", then Column A should= Column E

5. If Column C="SDD" and Column B="Y", AND Column F in that row = Column F in row above or below (and only if then), MATCH Column E above and/or below with the Hierarchy established in A2:A9 of Sheet2 and label Column with highest hierarchy displayed in those matching rows.
6. If Column A is still blank, then Column A should= Column E

I've gotten this far with a formula for steps 1-4:

Step 5 is a step to find MATCHING ID numbers in Column F (found by SDD- same day duplicates- in Column C and "Y"- yes on differnet reports- in Column B). I want to do a MATCH or something similar to compare the reports (Column E) to the hierarchy of reports on sheet 2, but I don't want it to compare E2 and E3 if F2 and F3 don't match. I only want to compare the Column E cells if the Column F cells match.

Here is a sample of the data with what I want to see in Column A already entered. (Sorry this isn't uploaded in the Excel format- can't get the Excel Jeanie on my work computer). If you want to see this in excel, just copy/paste it and then do Text to Columns with the delimiter of : in "other".

Report Assign to: Diff Rep.: PDD/SDD: Hier. Code: Report: ID #:
PDD: : PDD: A1: BF: ###0339:
DF: : SDD: C3: DF: ###9743:
DF: : SDD: C3: DF: ###9743:
DA: Y: SDD: C2: DA: ###7953:
DA: Y: SDD: C3: DF: ###7953:
DL: : : B1: DL: ###8470:
PDD: Y: PDD: C3: DF: ###1377:
PDD: Y: PDD: C4: DI: ###1377:
BF: Y: SDD: A1: BF: ###6863:
DI: Y: SDD: C4: DI: ###6863:
RI: Y: SDD: A1: RI: ###2841:
DA: Y: SDD: C2: DA: ###2841:
DA: Y: SDD: C3: DF: ###2841:

The Hierarchy Code on Sheet 2 looks like this, starting in Cell A2 and going to A9


I would like to know:
A) if it's even possible to do the hierarchy search ONLY when the F cells match and
B) if it's possible to put that statment into the formula already created for steps 1-4.

Any help with this would be INCREDIBLY appreciated.

I have the following code:

Option Explicit

Sub Consolidate()
    Dim MyPath As String
    Dim FilesInPath As String
    Dim MyFiles() As String
    Dim Fnum As Long
    Dim mybook As Workbook
    Dim CalcMode As Long
    Dim sh As Worksheet
    Dim ErrorYes As Boolean
    Dim DCLastRow As Integer 'DirectorCopy
    Dim MCLastRow As Integer 'Monthly Compiler
    Dim CMonth As String 'Compile Month
    Dim CYear As String 'Compile Year
    Dim Month As Integer
    Dim MonthFilter As String

    Dim center(18) As String
    center(1) = "Bardstown"
    center(2) = "Bothell"
    center(3) = "VCollinsville"
    center(4) = "El Paso"
    center(5) = "Evansville"
    center(6) = "Greensboro"
    center(7) = "VHeathrow"
    center(8) = "Joplin"
    center(9) = "Kennesaw"
    center(10) = "Lafayette"
    center(11) = "Malvern"
    center(12) = "VManhattan"
    center(13) = "VMansfield"
    center(14) = "VOttawa"
    center(15) = "VPonco City"
    center(16) = "VReno"
    center(17) = "VSioux City"
    center(18) = "VTerra Haute"
    Dim FileCount As Long
    Dim ScoringAve As Double
    Dim i As Long
'    If Cells(13, 4).Value = "January" Then Month = 1
'    If Cells(13, 4).Value = "February" Then Month = 2
'    If Cells(13, 4).Value = "March" Then Month = 3
'    If Cells(13, 4).Value = "April" Then Month = 4
'    If Cells(13, 4).Value = "May" Then Month = 5
'    If Cells(13, 4).Value = "June" Then Month = 6
'    If Cells(13, 4).Value = "July" Then Month = 7
'    If Cells(13, 4).Value = "August" Then Month = 8
'    If Cells(13, 4).Value = "September" Then Month = 9
'    If Cells(13, 4).Value = "October" Then Month = 10
'    If Cells(13, 4).Value = "November" Then Month = 11
'    If Cells(13, 4).Value = "December" Then Month = 12
'    CMonth = MonthName(Month, True)
    'This one line of code replaces the above 13 lines
    CMonth = Left(Cells(13, 4).Value, 3)
    CYear = Right(Cells(13, 7).Value, 2)

    'Fill in the pathfolder where the files are
    MyPath = "X:C&A Analysts TeamPF Process1 Tally & PF's Work in 
    For i = 1 To 18
'        'Add a slash at the end if the user forget it
'        If Right(MyPath, 1) <> "" Then
'            MyPath = MyPath & ""
'        End If
        'If there are no Excel files in the folder exit the sub
        MonthFilter = MyPath & center(i) & "*" & CMonth & " " & CYear & 
        FilesInPath = Dir(MonthFilter)
        If FilesInPath = "" Then
            MsgBox "No files found in " & center(i)
            GoTo ContinueLoop
        End If
        If FilesInPath <> "" Then
            FileCount = FileCount + 1
        End If
        'Fill the array(myFiles)with the list of Excel files in the folder
        Fnum = 0
        Do While FilesInPath <> ""
            If InStr(1, FilesInPath, CMonth & " " & CYear, vbTextCompare) Then
                Fnum = Fnum + 1
                ReDim Preserve MyFiles(1 To Fnum)
                MyFiles(Fnum) = FilesInPath
                FilesInPath = Dir()
            End If
        'Change ScreenUpdating, Calculation and EnableEvents
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        'Loop through all files in the array(myFiles)
        If Fnum > 0 Then
            For Fnum = LBound(MyFiles) To UBound(MyFiles)
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MyPath & center(i) & "" & 
                On Error GoTo 0
                If Not mybook Is Nothing Then
                    'Need to do the following:
                    'if lazy eye hasn't been run in directorcopy then run it
                    With mybook.Worksheets("DirectorCopy")
                        If .Cells(1, 1) = "" Then
                            Application.Run "DirectorFormat"
With Application.Run I'm getting the following error:

Cannot run the macro 'DirectorFormat'. The macro may not be available in
this workbook or all macros may disabled.

Here is the code for DirectorFormat:


    Dim TSLastPFRow As Integer   'Tally Sheet
    Dim TSPFTotal As Integer     'Tally Sheet PF
    Dim ZeroRow As Long, i As Long
    With Sheets("Tally Sheet")
        .Paste Destination:=Worksheets("DirectorCopy").Range("A1")
    End With
    With Worksheets("DirectorCopy")
        For j = 1 To 64
            .Shapes("Done! " & j).Cut
        .Cells.PasteSpecial Paste:=xlPasteValues, 
Operation:=xlPasteSpecialOperationNone, _
        SkipBlanks:=False, Transpose:=False
        'Find the last PF
        For i = 4 To Rows.Count Step 8
            If Cells(i, "A").Value = 0 Then
                ZeroRow = i
                Exit For
            End If

        TSLastPFRow = ZeroRow - 9
        TSPFTotal = (Val(Replace(Cells(TSLastPFRow, 1).Value, "_PF", "")))
        'Delete empty PFs at the bottom
        .Range(ZeroRow & ":515").Delete
        'Delete all title bars except the first one
        For i = (ZeroRow - 7) To 13 Step -8
        ActiveWindow.FreezePanes = True
    End With
End Sub
It's not set to private and I don't have my macros disabled. The code isn't
that long so I suppose I could just duplicate it in this macro but that seems
like "poor coding" to me if it's already somewhere else. What am I doing

Edit: Ok, figured it out. Needed to include the path. So instead of:

I'm using:

Hey all,

My last post may have been too confusing without some samples. I have attached to sample text documents. List 1 would represent Day 1 and List 2 represents Day 2. As you can see Day 2 has all the Day 1 data that I want to seperate from the new Day 2 data.

I am currently building a list of names, phone numbers, email, etc. I copy my list from a website I am using and paste it into my excel worksheet where the data is delimited in to individual colums. Each day the list gets bigger and bigger, but the website has no way of filtering old data from new data. I copy the list each day into my worksheet and have to use the "Remove Duplicates" tool because there will be doubles from the previous days.

I want a way to seperate the new data each day from the previous day. What I have done thus far is fill my cells with a color (yellow) before I enter the new data for the day. I copy and paste the new data into the worksheet and then sort the data alphabetically. I will have tons of duplicate entries. One in yellow and the other clear. Then I hit the remove duplicates and all of the clear duplicates are removed and the new data is left clear and the previously entered data is yellow. I then have to delete all of the yellow rows and that leaves me with the new data. We are talking about 1000's of rows so this process can take some time. I want to find an easier way to do this.

Anyone know a resolve for this issue.

I hope that makes sense.

Thanks in advance for the help.



Attached you will find a dummy excel book.

On Columns A, B, C I have original base information.
On Columns E, F, G, H, and I, I have entered information (usually from a copy/paste process).
On Column D I have the MATCH formula (matches E, to C...E,C$1:$C$number,0...)

What I have been doing, has been manually cutting A, B, C and inserting it in the row next to the C,E basically A, B, C move up and down fitting the match.

Is there any way to Macro or change the formula (or any idea) in such a way so that the manual cut/insert process goes faster. I have thousands of rows and multiple excel books (used for different cant combine) for which I have to match?

Here are the problems I have encountered that make recording a macro really touch (for me).
Sometimes there is so match for the info on E
Sometimes there are duplicates for the info on E with other E cells

It would be nice if the solution, if there is any, could be "universal" so that it could be used on other books(same principle, same 3 column deal).

Thank you very much

...on side note this is my first post if I did something wrong please say so.

hi all,

I am learning how to write a Function.
I have created a sub named Testing with twenty lines of codes in Excel 2002.
Since some of the codes contains a few lines of duplicate IF and Else statements,
I decided to make use of Function.
Here is the code:
Private Function finding(ByVal x As Double, ByVal whereToPasteAABB As Double) As Integer
 Dim isSame As Variant
If (Trim(Worksheets("RAW_Data").Cells(x, 1).Value) = Trim(Worksheets("arbitrary").Cells(whereToPasteAABB
- 1, 1).Value)) Then
        isSame = isSame + 1
        ElseIf (Trim(Worksheets("RAW_Data").Cells(x, 1).Value) =
Trim(Worksheets("arbitrary").Cells(whereToPasteAABB - 2, 1).Value)) Then
        isSame = isSame + 1
        finding = isSame
End Function

Sub testing()
Dim isSame As Variable
isSame = finding(1, 5)
MsgBox isSame
End sub
Since this is my first time writing a Function, I am not sure if
there's anything in the codes that I need to alter or change in the Function.
Please give me some advice and help

Thank you


Hi Guys. New here, so sorry if this was posted somewhere but I couldn't find it when searching. Anyway, I'm working on a large Excel file at work dealing with Marcellus Shale wells. I have spreadsheets for each year showing the wells permit #, production, name, company, etc. However, for some reason the spreadsheets were given to me in 6 month intervals for 2011 and 2010 (I.E. Jan-June 2011, then July-Dec 2011 are 2 separate files). My supervisor wants to know the number of producing and nonproducing wells per year, not per 6 months. So I combined all the data into one spreadsheet, and here's where I'm stuck.

Some wells appear twice in my list since they are in the first and second half of 2011. Out of those wells, some produced for both periods, some produced for one period, and some didn't produce at all. These wells share the same permit number, but not necessarily the same production data. I need a way to identify the duplicates and then decide whether to keep or delete it from the list. I can't just remove all duplicate permit #s, because I don't know if that function is removing wells that didn't produce originally but did produce by the end of the year, in which case I have to list them under producing wells. I also tried advanced filtering on the permit #s and copying unique values, but that isn't helping either.

I know this is probably confusing to read, but it's difficult to explain. In a nutshell, I need a way to identify duplicate values for a certain column (preferably by highlighting the entire row or something), and then I suppose I will go through by hand and decide whether to sort the duplicate values as producing or nonproducing. Ideally some type of macro that identifies a set of duplicates, checks for data in a certain cell (if both values >0 or <0 it deletes one of them, and if one value >0 and one value <0 it keeps the value >0), and then makes a decision of whether to keep or delete that row would be the best. But I'm not too strong at programming and I'll settle for the dirty way of fixing this if I can find a way to filter it.

Thanks for the help everyone.

I have a worksheet that has a project # on the left in Column A.
Project #1
Week 1 Rod (B2)
Week 2 Rod

Project #2
Week 1 Rod (B6)
Week 2 Joe

Then I may have up to 15 resource names, Rod, Joe, Fred, Ethyl....
What I would like to do is use code (VBA or macro) to conditionally format the Cells - Fill cell with Red and the text White in each cell when duplicates occur in the same column to tell me that I have already used Rod in Cell B2 and Cell B6 (Example above).

This needs to be done for every column and again I would have up to 15 names.

So I would like to use VBA or a macro to say:
Look in each column for duplicate names, but only in each colum and turn each cell where there is a duplicate name Red with white text to let me know that I already ahve allocated that resource.

Make sense??

I would also like to create a script that would say if you find the word completed in any cell then make the cell green with white text..

I am new to VBA in Excel so i would need some steps through on what to enter and how to enter it in Excel.

Thank You.


I've been searching all day to try to find an answer to my question.
Unfortuneately I haven't been able to find anything specific to my
issu. This may be because I am not using the right keywords, so
forgive me if this is a duplicate question of another posting.

Anyway, I have an excel spreadsheet of data that is sent to my group
each month. I am trying to automate the process of extracting the
data/columns that we need to import into access. 2 of the columns
contain fullnames. I need to split these columns into LastName and
FirstName which I have already done. However, in my code it the column
is chosen by the column range i.e (Q:Q). However, this is not always
true every month, so I wanted to be able to pick the column by the
column header instead. I am using the code below, but for some reason
it isn't moving across the column. It is moving down the rows. Can
someone please take a look at it and tell me what I am missing?


SearchValue = "GOwner" ' Set search value"

Set rng =
'lLastRow = rng.Row
lLastCol = rng.End(xlToRight).Column

Dim cVal As String
For i = 1 To lLastCol
MsgBox "Column = " & i
If Cells(i, "A").Value = SearchValue Then
MsgBox "Search value found at Column: " & i
End If
Next i

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