Lookup data from another excel files
 

Hi,

I use Excel 2003. I store my raw data in 2 seperate excel files(name "buy
wk20.xls" and "sell wk20.xls"). This 2 files get updated weekly, so the row
may reduce/increase.

I have a another excel file for management reporting purpose. There are 3
worksheets in this workbook (named "checkbook wk20.xls").

Based on the part numbers that appear on column A on each worksheet in the
checkbook, I need to be able to extract a certain data range from 2 raw files
and paste them to the correct worksheet & correct lines in the checkbook.

--------------------
1. Format on checkbook, "North A" worksheet,
on column a2-a4, I have "Part # ABC1234DEF ", "Buy", "Sell"
on column c2-f2, I have "ww20 ww21 ww22 ww23"
and buy/sell data (format=number) on c3-f4.

and repeat the same header pattern on cell a13, a24, a35, a46, a57, a68 and
continue until there's no more part# on this worksheet.

then on the second worksheet "South B",
the header pattern is still the same, except it starts on different cell
like a5, a16, a27, a38........

then on the third worksheet "East C",
also the same header pattern, but it starts on cell a3, a14, a25,....

2. Format on "buy wk20.xls", there is only 1 worksheet "buy wk20",
column e has the part num e.g. "ABC1234DEF", column g to j has the raw data.

3. Format on "sell wk20.xls", has 1 worksheet "sell wk20",
column a has the part num e.g. "ABC1234DEF", column b to e has the raw data.

----------------------

So:
I want to look for part num one by one in the checkbook on every worksheets,
match it to buy and sell files, copy the raw data on the same line and paste
them back to checkbook.

Buy data will come from column g-j from buy.xls and paste to column c3-f3 on
checkbook.xls

Sell data will come from column b-e from sell.xls and it will be pasted to
column c4-f4 on checkbook.xls.

if part number on checkbook.xls is found on a13, then buy data will go to
c14-f14, sell data will go to c15-f15.

Tricky part is that the part num is in the same cell with its header and the
space before and after part num is inconsistent.
there could be 1 or more spaces before and after part num.
We need to remove not just the space in front and at the back, but also the
"Part #" header, so that we can match it to the buy/sell file.

I can't do this using normal macro recording. Looks like complex
programming is required. Hope someone can help.

Regards,
choo

Lookup data from another excel files
 

Please try this:
(It will need som editing for different weeks, and possibly to distinguish
between North, South and East, but this may be a useful start.)
Sub Check()
Dim WBbuy As Worksheet, WBsell As Worksheet
Dim WBchk As Workbook
Dim sht As Integer
Dim irow As Long, irow2 As Long, NRows As Long, NRowsB As Long, NRowsS As Long
Dim Fnd As Boolean
Dim PtNo As String
'
Set WBbuy = Workbooks("buy wk20.xls").Sheets("buy wk20")
Set WBsell = Workbooks("sell wk20.xls").Sheets("sell wk20")
Set WBchk = Workbooks("checkbook.xls")
'
NRowsB = WBbuy.Cells(1, 1).SpecialCells(xlLastCell).Row
NRowsS = WBsell.Cells(1, 1).SpecialCells(xlLastCell).Row
For sht = 1 To 3 ' step thru each sheet in check book.
WBchk.Sheets(sht).Activate
If ActiveSheet.Name = "North A" Then irow = 2
If ActiveSheet.Name = "South B" Then irow = 5
If ActiveSheet.Name = "East C" Then irow = 3
NRows = Cells(1, 1).SpecialCells(xlLastCell).Row
For irow = irow To NRows Step 11 ' step thru parts on each sheet.
PtNo = Trim(Cells(irow, 1).Value) ' get part number and remove
leading and trailing spaces
PtNo = Trim(Right(PtNo, Len(PtNo) - 6)) ' remove text at start
and intervening spaces
' Now find part no in buy.
irow2 = 2
Fnd = False
While Not Fnd And irow2 <= NRowsB
If WBbuy.Cells(irow2, 5) = PtNo Then
Fnd = True
WBbuy.Cells(irow2, 7).Resize(1, 4).Copy
WBchk.ActiveSheet.Cells(irow + 1, 3)
Else
irow2 = irow2 + 1
End If
Wend
' Now find part no in sell.
irow2 = 2
Fnd = False
While Not Fnd And irow2 <= NRowsS
If WBsell.Cells(irow2, 1) = PtNo Then
Fnd = True
WBsell.Cells(irow2, 2).Resize(1, 4).Copy
WBchk.ActiveSheet.Cells(irow + 2, 3)
Else
irow2 = irow2 + 1
End If
Wend
Next irow
Next sht
MsgBox "Finished"
End Sub


"choo" wrote:

Hi,

I use Excel 2003. I store my raw data in 2 seperate excel files(name "buy
wk20.xls" and "sell wk20.xls"). This 2 files get updated weekly, so the row
may reduce/increase.

I have a another excel file for management reporting purpose. There are 3
worksheets in this workbook (named "checkbook wk20.xls").

Based on the part numbers that appear on column A on each worksheet in the
checkbook, I need to be able to extract a certain data range from 2 raw files
and paste them to the correct worksheet & correct lines in the checkbook.

--------------------
1. Format on checkbook, "North A" worksheet,
on column a2-a4, I have "Part # ABC1234DEF ", "Buy", "Sell"
on column c2-f2, I have "ww20 ww21 ww22 ww23"
and buy/sell data (format=number) on c3-f4.

and repeat the same header pattern on cell a13, a24, a35, a46, a57, a68 and
continue until there's no more part# on this worksheet.

then on the second worksheet "South B",
the header pattern is still the same, except it starts on different cell
like a5, a16, a27, a38........

then on the third worksheet "East C",
also the same header pattern, but it starts on cell a3, a14, a25,....

2. Format on "buy wk20.xls", there is only 1 worksheet "buy wk20",
column e has the part num e.g. "ABC1234DEF", column g to j has the raw data.

3. Format on "sell wk20.xls", has 1 worksheet "sell wk20",
column a has the part num e.g. "ABC1234DEF", column b to e has the raw data.

----------------------

So:
I want to look for part num one by one in the checkbook on every worksheets,
match it to buy and sell files, copy the raw data on the same line and paste
them back to checkbook.

Buy data will come from column g-j from buy.xls and paste to column c3-f3 on
checkbook.xls

Sell data will come from column b-e from sell.xls and it will be pasted to
column c4-f4 on checkbook.xls.

if part number on checkbook.xls is found on a13, then buy data will go to
c14-f14, sell data will go to c15-f15.

Tricky part is that the part num is in the same cell with its header and the
space before and after part num is inconsistent.
there could be 1 or more spaces before and after part num.
We need to remove not just the space in front and at the back, but also the
"Part #" header, so that we can match it to the buy/sell file.

I can't do this using normal macro recording. Looks like complex
programming is required. Hope someone can help.

Regards,
choo