• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combining data...

Hamish

Member
Hi All,

I have recently undertaken a VBA course and I am gradually starting to get a hold of how VBA works.

My current problem is that I would like to tables of data to combine. Whilst combining I'd like the tables to be scanned for the same items and if the same item code has the same quantity the one of the lines is kept and the duplicate is removed. If the items are the same but the quantities differ then they are highlighted and finally if the item is new or unmatched it would be kept.

Any help would be greatly appreciated.
 
Hi Hamish

It would be nice to see your data, both before the code has run and a section showing what it should look like. I can provide you with some coding that does this very quickly but will most likely get the format wrong. Chandoo forums have upload facilities for files so you are all good there.

Take care

Smallman
 
Hi Smallman,

I couldn't upload the full files, however I have put the two worksheets together so you can see the original and the new sheet to be merged.

Thanks
 

Attachments

  • consolidated BOQs.xlsx
    62.3 KB · Views: 5
I can't quite get this working???

Option Explicit

Sub GetFiles()

Dim strEst1 As String
Dim strEst2 As String

strEst1 = Application.GetOpenFilename(filefilter:="Excel Files (*.xls*), *.xls*", Title:="Select the FIRST estimate")
strEst2 = Application.GetOpenFilename(filefilter:="Excel Files (*.xls*), *.xls*", Title:="Select the SECOND estimate")

If strEst1 <> "False" And strEst2 <> "False" Then
Workbooks.Open strEst1, False 'This is workbooks.count - 2
BreakAllLinks ActiveWorkbook

Workbooks.Open strEst2, False 'This is workbooks.count - 1
BreakAllLinks ActiveWorkbook

Workbooks.Add 'This is workbooks.count

Workbooks(Workbooks.Count - 2).Worksheets("ss bom").ListObjects(1).Unlist
Workbooks(Workbooks.Count - 2).Worksheets("ss bom").Range("a1").CurrentRegion.Copy
ActiveCell.PasteSpecial xlPasteColumnWidths
ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats

Dim newHeadings As Integer

newHeadings = ActiveWorkbook.Worksheets(1).Range("a1").End(xlDown).Offset(1, 0).Row

Workbooks(Workbooks.Count - 1).Worksheets("ss bom").ListObjects(1).Unlist
Workbooks(Workbooks.Count - 1).Worksheets("ss bom").Range("a1").CurrentRegion.Copy
ActiveCell.End(xlDown).Offset(1, 0).Select

ActiveCell.PasteSpecial xlPasteColumnWidths
ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats

Rows(newHeadings).Delete

Workbooks(Workbooks.Count - 2).Close savechanges:=False
Workbooks(Workbooks.Count - 1).Close savechanges:=False

Range("a1").Select

ActiveSheet.ListObjects.Add(xlSrcRange, Range("a1").CurrentRegion, , xlYes).Name = _
"CombinedEstimates"

End If



End Sub
 
Back
Top