Extracting datas from one list not on another list

[Dani Lima]

Here is my problem: I have a master worksheet that is feed by another
worksheet every mounth. I have to check on the second worksheet the mames of
the clientes that isn't on master worksheet and than extract them to the
first empty row on master worksheet. After that I have to extract the
clients' deliveries data on the second worksheet to its match on the master
worksheet. Could you help me?

 

[Otto Moehrbach]

Dani
This would have to be done with VBA. Provide details about the layout
of the data in both sheets. That is, what columns have the names? What
columns do you want copied from one sheet to what columns in the master
sheet? What rows have the headers and in what rows do the data start? Do
you have more than one "another" sheet? Do you want the master sheet sorted
after the copying is done? HTH Otto

 

[Dani Lima]

Otto,

Both worksheets look like that:

colA colb Colc cold
2 Clients'name Accur % total ord total deliv.
3 051 -Danielle
4 098 - Andre
5 110 - Aliine

I wanna copy from the "another" worksheet the names on columm A that aren't
on the Columm A of the master worksheet to the end of the columm A of
worksheet. The rows with the headers are always the third and the data starts
always on the fourth.
Yeah, I have more than one "another" worksheet. But they each one just feed
the mastersheet once, at its respective month.
If possible I'd like them sorted.

thks

Dani

"Otto Moehrbach" escreveu:

 

[bman]

Otto,

Both worksheets look like that:

colA colb Colc cold
2 Clients'name Accur % total ord total deliv.
3 051 -Danielle
4 098 - Andre
5 110 - Aliine

I wanna copy from the "another" worksheet the names on columm A that aren't
on the Columm A of the master worksheet to the end of the columm A of
worksheet. The rows with the headers are always the third and the data starts
always on the fourth.
Yeah, I have more than one "another" worksheet. But they each one just feed
the mastersheet once, at its respective month.
If possible I'd like them sorted.

thks

Dani

"Otto Moehrbach" escreveu:

 

[bman]

Take a look at the "Match" function. I am not sure how much flexibility you
have with the master. I take it that the "second" sheet you mentioned will
have some existing customesr as well as new ones, before the master is
updated.

There is a help on this at the following MS site, so no need for me to go
over it.
http://office.microsoft.com/en-us/excel/HA011039151033.aspx

It would be best if you could have your master list and the montly updates
both in the same sheet. in separate columns or arrays

I have used the Hlookup function to do similar tasks. Just be careful when
you sort, to copy the appropriate cell formulas with the resulting result,
before you sort.

Hope this helps.

 

[Otto Moehrbach]

Dani
Did "bman" give you what you needed or are you waiting for me?
What do you mean by "But they each one just feed the mastersheet once, at
its respective month"? Expound on that a little. Where is the month in
your data? Do you want to do this just once a month, and if so, how can you
tell which sheet to copy from in what month? HTH Otto

 

[Dani Lima]

Otto

I've just read the answer from "bman", I think it will work, but I was
thinking in something more quick 'cause I have to do it every month in a data
base with at least 20000 rows.

About your questions, I think I don't explain properly, the masterworksheet
will look like this one under. The another one worksheet looks like the first
I draw; it will feed the masterworksheet just with the datas "tot ord" and
"tot deliv" on its columms C and D to the columms "total ord" and 'tot deliv"
from the respective month. The another worksheet always has the same name:
"DPL Clientes Brazil" and it is replaced every month, so I think I won't have
problem in tell what sheet to copy from. Yesterday I've tried the the
formula: if(iserror(procv(......;.....;...;....));ext.text(.....;..;...);"")
but It didn't work properly because of the if not clause, as a result I had
several raws with a space...
I'll keep trying, If i didn't get anything I'll do it manually with the
match clause.

tks

Dani

colA colb Colc cold cole
Colf cold
1 Jul/08
Aug/08
2 Clients'name Accur % total ord tot deliv Accur % total
ord tot deliv
3 051 -Danielle
4 098 - Andre
5 110 - Aliine


"Otto Moehrbach" escreveu:

 

[Otto Moehrbach]

Dani
This macro should do what you want.
I assumed that you have a sheet named "Master". The "other" sheet is named
"DPL Clientes Brazil". Place this macro in a regular module. View this
post in full screen before you copy this macro. Try this macro on a copy of
your file first. If you don't know where to place this macro or you have
trouble with it, send me an email and I'll send you the small file I used in
developing this macro. My email address is (e-mail address removed).
Remove the "extra" from this address. HTH Otto
Sub UpdateTheMaster()
Dim rColAM As Range
Dim rColADPL As Range
Dim Dest As Range
Dim i As Range
Application.ScreenUpdating = False
Sheets("Master").Select
Set rColAM = Range("A4", Range("A" & Rows.Count).End(xlUp))
Set Dest = Range("A" & Rows.Count).End(xlUp).Offset(1)
With Sheets("DPL Clientes Brazil")
Set rColADPL = .Range("A4", .Range("A" & Rows.Count).End(xlUp))
End With
For Each i In rColADPL
If rColAM.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing Then
i.Copy Dest
i.Offset(, 2).Resize(, 2).Copy Dest.Offset(, 2)
Set Dest = Dest.Offset(1)
End If
Next i
Range("A3", Dest).Resize(, 4).Sort Key1:=Range("A4"),
Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Application.ScreenUpdating = True
End Sub