Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Populating Master Spreadsheet w/o Access

03 Oct 2011   #1
mjm6783

XP Professional, 7 Professional
 
 
Populating Master Spreadsheet w/o Access

Hey everyone,

I've got kind of a tricky one here. I need to have client information from three separate excel spreadsheet, auto populate a master list. I don't have the option of using access, so I'm left trying to use macros and visual basic programming.

The issue is really the sort and filter functions. I can bring the data from all three sources, and each client has a unique ID code. But replacing duplicate records and merging the rows from different spreadsheets is proving difficult.

Does anyone have any suggestions of an alternate way to achieve this? Or even some tips on the excel functions I should be using? Anything is appreciated.


My System SpecsSystem Spec
.
05 Oct 2011   #2
Irene

Windows 7 Home Premium 64bit
 
 

There could be some useful information here:
Top ten ways to clean your data - Excel - Office.com

Filter for unique values or remove duplicate values - Excel - Office.com

Quote:
Third-party providers

The following is a partial list of third-party providers that have products that are used to clean data in a variety of ways.
Provider Product
Add-in Express Ltd. Advanced Find & Replace
Merge Cells Wizard
Add-Ins.com Duplicate Finder
AddinTools AddinTools Assist
J-Walk & Associates, Inc. Power Utility Pak Version 7
PATools PATools Advanced Find Replace
Vonnix Excel Power Expander 4.6
WinPure ListCleaner Lite
ListCleaner Pro
Clean and Match 2007
My System SpecsSystem Spec
10 Oct 2011   #3
mjm6783

XP Professional, 7 Professional
 
 

Ya, I'm aware of the filter sort functions. The real issue had to do with automating the procedure across three spreadsheets and three separate users over an intranet server.

I ended up getting some help from a guy over at excelforum. He worked with me to write some visual basic macros that are working pretty well. We still have a couple of kinks to work out, but I can post the basic code once it's up and running, in case anyone else has a similar problem.
My System SpecsSystem Spec
.

11 Oct 2011   #4
Irene

Windows 7 Home Premium 64bit
 
 

Quote   Quote: Originally Posted by mjm6783 View Post
Ya, I'm aware of the filter sort functions. The real issue had to do with automating the procedure across three spreadsheets and three separate users over an intranet server.

I ended up getting some help from a guy over at excelforum. He worked with me to write some visual basic macros that are working pretty well. We still have a couple of kinks to work out, but I can post the basic code once it's up and running, in case anyone else has a similar problem.
Thank you for posting back, mjm6783. I am sure that others with the same problem would be very grateful for sharing your information.
My System SpecsSystem Spec
12 Oct 2011   #5
mjm6783

XP Professional, 7 Professional
 
 

So this is the MAIN section of code which is copied, (although altered slightly) for each of the workbooks which populate the master. It's set up for my specific needs in terms of specific column transfers and sorting/overwriting. There is some minor additional code, but it's unrelated to the functions shown here. Hope this helps someone out there.

Sub RunMe()
Dim wbTarget As Workbook
Dim wbSource As Workbook
Dim MyPath As String
Dim sLR As Long
Dim tLR As Long
Dim sCell As Range
Dim findMe As Range
Dim i As Long
MyPath = ActiveWorkbook.Path
Set wbSource = ThisWorkbook

On Error Resume Next
Set wbTarget = Workbooks("Master.xlsx")
If wbTarget Is Nothing Then 'Not Open
Application.Workbooks.Open (MyPath & "\" & "Master.xlsx")
On Error GoTo 0
Set wbTarget = Workbooks("Master.xlsx")
Else
End If
With wbTarget.Sheets("Sheet1")
tLR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
End With

For i = 1 To wbSource.Sheets.Count
With wbSource.Sheets(i)
sLR = .Range("A" & .Rows.Count).End(xlUp).Row
If sLR <= 1 Then GoTo skip
End With
For Each sCell In wbSource.Sheets(i).Range("B2:B" & sLR)
If Not wbSource.Sheets(i).Range("Z" & sCell.Row) = "X" Then
Set findMe = wbTarget.Sheets("Sheet1").Range("B2:B" & tLR).Find(What:=sCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)

If findMe Is Nothing Then
wbTarget.Sheets("Sheet1").Range("B" & tLR).Value = sCell.Value
wbTarget.Sheets("Sheet1").Range("A" & tLR).Value = sCell.Offset(0, -1).Value
wbTarget.Sheets("Sheet1").Range("A" & tLR).Offset(0, 5).Value = sCell.Offset(0, 1).Value
wbTarget.Sheets("Sheet1").Range("A" & tLR).Offset(0, 6).Value = sCell.Offset(0, 2).Value
wbTarget.Sheets("Sheet1").Range("A" & tLR).Offset(0, 7).Value = sCell.Offset(0, 3).Value
wbTarget.Sheets("Sheet1").Range("A" & tLR).Offset(0, 8).Value = sCell.Offset(0, 4).Value
wbTarget.Sheets("Sheet1").Range("A" & tLR).Offset(0, 9).Value = sCell.Offset(0, 5).Value
wbTarget.Sheets("Sheet1").Range("A" & tLR).Offset(0, 10).Value = sCell.Offset(0, 6).Value
tLR = wbTarget.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
wbSource.Sheets(i).Range("Z" & sCell.Row) = "X"
Else
wbTarget.Sheets("Sheet1").Range("B" & findMe.Row).Value = sCell.Value
wbTarget.Sheets("Sheet1").Range("A" & findMe.Row).Value = sCell.Offset(0, -1).Value
wbTarget.Sheets("Sheet1").Range("A" & findMe.Row).Offset(0, 5).Value = sCell.Offset(0, 1).Value
wbTarget.Sheets("Sheet1").Range("A" & findMe.Row).Offset(0, 6).Value = sCell.Offset(0, 2).Value
wbTarget.Sheets("Sheet1").Range("A" & findMe.Row).Offset(0, 7).Value = sCell.Offset(0, 3).Value
wbTarget.Sheets("Sheet1").Range("A" & findMe.Row).Offset(0, 8).Value = sCell.Offset(0, 4).Value
wbTarget.Sheets("Sheet1").Range("A" & findMe.Row).Offset(0, 9).Value = sCell.Offset(0, 5).Value
wbTarget.Sheets("Sheet1").Range("A" & findMe.Row).Offset(0, 10).Value = sCell.Offset(0, 6).Value
tLR = wbTarget.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
wbSource.Sheets(i).Range("Z" & sCell.Row) = "X"
End If
End If
Next sCell

skip:
Next i

Application.ScreenUpdating = False
With wbTarget.Sheets("Sheet1")
tLR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("A2:A" & tLR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With
With wbTarget.Sheets("Sheet1").Sort
.SetRange Range("A1:K" & tLR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True

Flag = True
wbTarget.Close True
Set wbTarget = Nothing
Set wbSource = Nothing

End Sub
My System SpecsSystem Spec
Reply

 Populating Master Spreadsheet w/o Access




Thread Tools




Similar help and support threads
Thread Forum
How long to start populating Prefetch folder?
Because I got a new motherboard, I had to do a clean reinstall of Win 7 Pro 32-bit SP1 on June 9. Here it is June 21 and my Prefetch folder has zero program entries. In addition, possibly related, from the time I boot my PC until all the hard drive thrashing is finished is 2 min, 15 sec. Soluto...
General Discussion
A lot of ##### in my spreadsheet.
I have decided to download my bank account monthly balance sheet,Got it into a spreadsheet okay But the last column with the total balance I get a lot of these ########. With no figures. Example 100.00 I get ####### How can I solve this please,The other column's with input's show the figures...
Microsoft Office
Spreadsheet
Hello.Just installed Open Office,Now trying to use the spreadsheet system. I thought that I would make an address book up of family names etc,Now when it comes to entering their telephone ie:01683***** when its in the column the nought aint there can anyone explain to me why this is and is there a...
Microsoft Office
A spreadsheet moron looking for help.
What is the formula that will calculate the percentage of random cells?
Microsoft Office
Start menu icons populating slowly, help?
When I click the start button and the menu opens, I notice that many of the icons take extra time to load. What can be done about this? :geek: FYI, this does not happen all the time, but it is kind of annoying Tried this:...
General Discussion
Populating the Server
I notice whenever I need to use the server it doesn't immidately show as a share on my network. All the other computers show up but it takes up to a minute or longer for the WHS to show up. Any suggestions to speed up the process?
Network & Sharing


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 22:41.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App