Hi Pete / Michelle

Michelle sent me 2 sample workbooks. I had to make some substantial
changes to the code I posted originally, and sent back to Michelle. She
has just mailed me back to thank me, and whilst there are some other
difficulties she still needs to resolve. I had asked her when mailing
back, would she mind if I posted details of the changes, in order to
"close the thread" and also so "Pete would see what has happened on
return from his vacation". She agreed and said this thread can now be
"closed".

Basically the data in Pricelists was "dirty". There were many case where
Lot No appeared in column A just once, with many Batch numbers in
successive rows. There were also lots of blank rows throughout each
Sheet in Pricelist.
One Brand sheet, had an extra column E with quantities recorded.

I read all data across from each sheet as per original code, but picking
up columns A to E from all sheets.
I then inserted missing Lot No.s for all relevant lines.
I applied an Autofilter to the table, selected blank rows and deleted
them.
I inserted a column at C and made a concatenation of A2 & "|" &B2 ( I
like to insert the pipe character "|" between concatenations so there
can be no confusion between whether 36001 is 360 and 01 or 36 and 001.
I created a range called Mydata for columns C to F for the range of rows
within the cleaned data on the Summary sheet.

On the sheet called Data in Michelle's Summary file, she had set up a
table with headings in A4 to F4 of
Item No., Lot No., Batch No., Quantity, Description, Unit Cost
I amended her formulae in E5 and F5 to
=IF(B5="","",IF(ISNA(VLOOKUP($B5&"|"&$C5,MyData,2, 0)),
"Not Present",VLOOKUP($B5&"|"&$C5,MyData,2,0)))
with the Offset in F5 being changed from 2 to 3

At the beginning of the code, Autofilter is switched off for the Summary
sheet, Column C containing the concatenation is deleted, all rows from
row 2 to the last row of data are cleared before the procedure goes
through the steps as outlined above.

Pete have a great holiday.
--
Regards

Roger Govier


"Pete_UK" wrote in message
ps.com...
Hi Michelle,

Hope you managed to get something working.

Pete, from Canada

MichelleS wrote:
Thank you Roger for all your help with this......although I am not
that
versed with the below codes and am having difficulty using them.
Will it be
possible to forward you the actual 2 workbooks (Estimates.xls and
Prices.xls)
via email to see what I am visually trying to relay?

Also, Pete thank you as well, even through your travels I still would
like
to express my appreciation. Hoping a safe return.

MichelleS

"Roger Govier" wrote:

Hi Pete and Michelle

Roger - could you modify your code so that after it copied the
six
sheets from the Prices file it inserted a new column C and
concatenated
A and B (Product and Batch) together?

I can if required, and I had played with using concatenation as a
method
for Michelle to achieve her objective without resorting to VBA -
which
would have been perfectly feasible.

With the VBA solution, however, she has all of the data she needs
without having to resort to any further Lookups of any description
hence
I do not really think it is required.
Marking the top row and applying DataFilterAutofilter will permit
the
selection of any range of products, all of which will have their
Description and Price.

I have noticed 2 errors in my original code
Set Spath = "C:\Excel\Ngroups\" ' <=== Change to suit
Should not have the word Set at the beginning, it should just read

Spath = "C:\Excel\Ngroups\" ' <=== Change to suit

Also, I can get inconsistent results with the line
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
This should be amended to
lastrow = DestSheet.Cells(Rows.Count, 1).End(xlUp).Row

I have repeated the complete code below, with the inclusion of
Pete's
request for the concatenation.
I have remmed out the 2 sections which have been added for this
purpose,
removing the single quote in front of the lines where shown will
cause
them to be invoked.

Sub ColateData()
Dim Wbook As Workbook, Spath As String
Dim SourceSheet As Worksheet, DestSheet As Worksheet
Dim addrow As Long, lastrow As Long
Dim source As Range, dest As Range

Application.ScreenUpdating = False
ThisWorkbook.Sheets("Summary").Select
Set DestSheet = ThisWorkbook.Sheets("Summary")
With DestSheet
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
If lastrow = 1 Then lastrow = 2
Rows("2:" & lastrow).Clear

'Remove single quotes at beginning of next 3 lines if Concatenation
of
'Product and Batch is required

'If UCase(Range("C1")) < UCase("Description") Then
'Columns("C:C").Delete
'End If

End With
Spath = "C:\Excel\Ngroups\" ' <=== Change to suit
Set Wbook = Workbooks.Open(Spath & "Prices.xls") ' line amended to
remove Set

For Each SourceSheet In Wbook.Worksheets
With DestSheet
lastrow = DestSheet.Cells(Rows.Count, 1).End(xlUp).Row ' Destsheet
added
to line
End With

addrow = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set source = SourceSheet.Range("A2:D" & addrow)
Set dest = DestSheet.Range("A" & lastrow + 1)
source.Copy dest
DestSheet.Range("E" & lastrow + 1 & ":E" & lastrow +
addrow - 1)
= SourceSheet.Name

Next

'Remove single quotes at beginning of next 3 lines if Concatenation
of
'Product and Batch is required
' DestSheet.Columns("C:C").Insert
' DestSheet.Range("C2").FormulaR1C1 = "=RC[-2]&""|""&RC[-1]"
' DestSheet.Range("C2").Copy DestSheet.Range("C3:C" & lastrow +
addrow - 1)


Wbook.Close Savechanges:=False
ThisWorkbook.Save

Application.ScreenUpdating = True
End Sub

Post back if you have any difficulties.


--
Regards

Roger Govier


"Pete_UK" wrote in message
oups.com...
Hi Michelle and Roger,

sorry for not responding sooner - have been getting ready to go
away
first thing in the morning.

Roger - could you modify your code so that after it copied the
six
sheets from the Prices file it inserted a new column C and
concatenated
A and B (Product and Batch) together?

Michelle - if Roger joins the two fields together in a new column
C of
your Summary sheet, then assuming there are no duplicates you can
enter
these formulae in columns C and D of your Estimates sheet:

C2: =IF(ISNA(VLOOKUP(A2&B2,Summary!C:F,2,0)),"Not
Present",VLOOKUP(A2&B2,Summary!C:F,2,0))
D2:
=IF(ISNA(VLOOKUP(A2&B2,Summary!C:F,3,0)),"",VLOOKU P(A2&B2,Summary!C:F,3,0))

and you can add a column E with "Brand" in E1:

E2:
=IF(ISNA(VLOOKUP(A2&B2,Summary!C:F,4,0)),"",VLOOKU P(A2&B2,Summary!C:F,4,0))

If a match isn't found you will get the message "Not Present" in
column
C and blanks in D and E, otherwise you will get the Description,
Price
and Brand returned in these columns. If there are duplicates,
then
VLOOKUP will find the first of these. Copy the formulae down for
as
many entries as you have in A and B.

Hope this helps.

Pete