extract key words/data from multiple files -dump in new worksheet

[MikeR-Oz]

I ahve posted previously and as I'am gaining a clearer picture of what I need
to do I thought I should repost here with better explanation.

Given that my excel skills are limited at present, I have a task for work
that I hope you can help with.


I want to be able to search for a store name that "may" appear in a cloumn
in a seperate excel file and with tha store name grab the data that is
associated with it for another column and then place this data in a new
worksheet and repeat the process so that each grab / extract and dump, places
the new data next to the first extract. BUt with every sucessive dunp I only
want the data that is in the 2nd comun that relates to the 1st column store.
I will explain the layout in more detail below:- But is there a macro or some
tool that I can run that will do this?


A series of saved excel files that are based on weeks - Monday to Sunday.
The sheets are arranged with 1st column being a store with various stores
running down in rows (not each week shows the same stores however there are
many times where the same store is shown in seperate workbooks). The days of
the week are also aranged in columns- Mon, Tue Wed, Thur, etc.

The cells below each day for the store shows total sales .

So the workbook for the week will show the total sales per store per day.

E.g Store A(columnA) and Monday Week 1 (Clomumn B),Monday Week 2 (Clomumn
C),Monday Week 3 (Clomumn D)etc

Then Store A Tuesday Week1, (Clomumn B),Tuesday Week 2 (Clomumn C),Tuesday
Week 3 (Clomumn D)etc

Etc Etc...


Now I have multiple weeks and want to see what TRENDS store (A) does on
Mondays ONLY and then Tuesday ONLY etc over the many weeks in order to see if
one day is better than another for that store and then repeat for each store
(A) to (L)

Then do for each store and then clusters of stores which make up regions.

Is this possible easily?

Thanks

Michael
Was this post

 

[davesexcel]

Here's a good article on VLOOKUP



http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=44

 

[MikeR-Oz]

Thanks "davesexcel" I will read and hopefuly be able to follow, but I gatehr
with your experience that V Look Ups is the way to go for the task I have .?
Mike

 

[flummi]

Your description leaves a few things to the imagination.

Is this what you have?

Workbook for week 1 (separate file!)

Column A___Column B___Column C___Column D
Store__A___sales Mon___sales Tue___sales Wed etc
Store__B___sales Mon___sales Tue etc

Workbook for week 2 (separate file!)

Column A___Column B___Column C___Column D
Store__B___sales Mon___sales Tue___sales Wed etc
Store__C___sales Mon___sales Tue etc

Workbook for week 3 (separate file!)

Column A___Column B___Column C___Column D
Store__A___sales Mon___sales Tue___sales Wed etc
Store__E___sales Mon___sales Tue etc

etc.

Or is this your design?

Workbook for no specific week (separate file!)

Column A___Column B___Column C___Column D
Store:______Week1_____Week2______Week3 etc
Store__A___sales Mon___sales Mon___sales Mon etc
Store__A___sales Tue ___sales Tue etc
Store__F___sales Mon___sales Mon___sales Mon etc
Store__F___sales Tue ___sales Tue etc

Another workbook for no specific week (separate file!)

Column A___Column B___Column C___Column D
Store:______Week1_____Week2______Week3 etc
Store__X___sales Mon___sales Mon___sales Mon etc
Store__X___sales Tue ___sales Tue etc
Store__F___sales Mon___sales Mon___sales Mon etc
Store__F___sales Tue ___sales Tue etc

Hans

 

[MikeR-Oz]

Thank you Hans for taking the time to help.


I have seperate excel files each with 1 only worksheet that looks :

File is identifiedd by it being named as the week ending that the columns
cover and above Column C in a roew of its own the week ending date is shown
e.g 04/9/2005


Call this Week 1
Column A______ Column B________Column C______ etc
Store Name Monday Tuesday Wednsday
Store X 56 (blank)
33
Store B 99 65
12
Store A (Blank) 45
(Blank)

Next File same thing repeated but there may be no Store B or A only X and
new stores such as Store D, G , K etc etc


Call this week 2
Column A______ Column B________Column C______ etc
Store Name Monday Tuesday Wednsday
etc etc
Store X 23 55
( Blank)
Store D 16 (Blank)
22
Store K (Blank) ( Blank)
(Blank)


etc etc for 40 otehr files and week -endings

So I need to grab Store A - Mondays from any of the Files that Store A has
data and if store A doesn't have any data or an entry for that week still
dump a blank space in the next adjacent column in a new worksheet for all
Mondays:-


Column A______ Column B________Column C______ etc
Store Name Monday Wk1 Monday Wk2 Monday Wk3
etc
Store X 56 23 (
Blank)
Store B 99 (Blank)
(Blank)
Store A (Blank) ( Blank ) 23


etc etc etc

Is a VLOOKUP the way to go? HGow do i VLOOKUP just the Monday for a
particular store across all the seperate files and then extract the data to a
new work sheet created that adds the the new column as it goes for each
successive :MOndays"?

Thanks
Mike

 

[Roger Govier]

Hi Mike

I still maintain that in the long term you would be better off with all
your data in one file.
You now mention you have about 40 files (weeks of data).
The number of rows of data for each file is fairly small, and it is a
not too difficult one-off task to copy and paste that data to a single
Sheet in a single workbook.
As I said before, you would just need to add an extra column with week
number (or weekending date as you currently have it stored in each
workbook) to the rows that have come from each workbook.
Once you have done this, all that you want to do becomes very easy with
Pivot Tables, or if you don't feel comfortable with setting up Pivot
tables, then with SUMIF or SUMPRODUCT Formulae.
If you still need to keep future data in separate workbooks for other
reasons, then do so, but just copy that small block of data each week to
your "Master" workbook.

Using formulae for example to get all the Monday sales for Store A it
would then be
=SUMIF(A:A,"Store A,B:B) to get Tuesday, just change B:B to C:C etc,
through to H:H for Sunday
Or better still, in you Master workbook set up a summary sheet on Sheet2
and enter you days of the week in B1:H1
Enter your Store names in A2:A?? where ?? is the last row number that
contains a store name.
In cell B2 enter
=SUMIF(Sheet1!$A:$A,$A2,Sheet1B:B)
Copy this formula cross through cells C2:H2 and then copy cells B2:H2
down through cells B2:B??

The effort of making the one-off copy of data initially will be well
repaid with the simplification of analysing and maintaining analysis for
the future, compared with trying to extract data from a whole series of
different workbooks.

 

[Roger Govier]

Apologies

=SUMIF(A:A,"Store A,B:B)

should of course read
=SUMIF(A:A,"Store A",B:B)

 

[flummi]

Hi Mike,

In case you want to stick to the separate files design I will post a
VBA procedure that does it. Just give me a couple of hours.

Regards

Hans

 

[MikeR-Oz]

Thank you Hans and Roger,


I will be doing an edvanced Excel 1 day course in 7 weeks time so I hope
that I will understand more of what I ask then.

But Roger I will do as you have suggested , (HANS see below) however I
know nothing of pivot tables and wioll try to read up on them, tahnk you for
the sumif formulae. I need to be able to see TRENDS with the data - so I
will need Store A in Colun A1 and the Mondays for each week listed across the
other columns b1, c1 d1 etc (all Mondays ) with the store sale info in those
for Mondays and then ALL Tuiesdays etc THEN I can line graph to see if there
are any trends in the specific days of the week etc. Will the sumif function
do this??

HANS,
please if you do not mind I would like to see the VBA and how I can use
this to achieve the result I am after and also set me on the path of more
leaning for future endeavours.

I actually thought that the data may have been best in Access , but again I
have only begginer experience with that.

Appreciate your help.

Mike

 

[Roger Govier]

Hi Mike

Pivot tables will work best for you and are worth the investment in time
to understand them.

To do what you want with formulae, then, having got all your data to
Sheet1, on Sheet2 set it up with Store Name in column A, and in row 1
starting at B1 and going across the page, put the dates for your weeks,
the same as that which you will have in column I of sheet1

Then in B2 enter
=SUMPRODUCT(--(Sheet1$A$2:$A$1000=A2),--(Sheet1!$I$2:$I$1000=B$1),Sheet1!B$2:B$1000)
Copy across through C2:H2
Copy B2:H2 down through B3:B?? where ?? is your last row with a store
name.

You will then have weekly sales for each store going across the page.

 

[flummi]

Hi Mike,

Here's a first shot:

I have files called "callsxx.xls" where xx is the week number. these
files reside in a folder "c:\Excel_tests\Calls\". Both the path and the
file names can be set in the procedure below.

Then I have a consolidation workbook with a combobox in B1 which links
into B1 and is populated with the names of the week days from a table
on sheet "Sheet3".
In E1 I have the number of the weekday corresponding to the name chosen
from the combobox.

Formula is: =OFFSET(Sheet3!A1,MATCH(B1,Sheet3!A1:A7,0)-1,1)

The table looks like this:

Monday 1
Tuesday 2
Wednesday 3
Thursday 4
Friday 5
Saturday 6
Sunday 7

Row 3 has the headers "Store" and the 52 weeks.

Rows 4 to n will be populated from the "calls" files by the VBA
procedure.

At the top I have a command button "Consolidate" which holds the code
of the VBA procedure.

After consolidation all rows are sorted in ascending order of store
name.

The design of the "calls" files is like this:

week end 26.03.2006 13
Store Monday Tuesday Wednesday Thursday Friday Saturday Sunday
B 12 33 4 22
C 45 20 45
L 55 12 88 14 20
X 40

D1 contains the week number corresponding to the week end date. Formula
is: =WEEKNUM(C1,2)

If you write me an email with or without your workbook files I can send
you the test files if you like.

Here's the VBA procedure:

Private Sub CommandButton1_Click()

'Set the search path and the file name to search for

SearchPath = "C:\Excel_tests\Calls"
FileToSearch = "call*.xls"

'determine the number of rows occupied in the consolidate worksheet

AnzConsA =
Workbooks("Consolidate.xls").Worksheets(1).Cells(Rows.Count,
"a").End(xlUp).Row

'clear the consolidate worksheet

Workbooks("Consolidate.xls").Worksheets(1).Range("4:" +
CStr(AnzConsA)).Clear

'get the names of the files in search path tha meat the file name

With Application.FileSearch

.NewSearch
.LookIn = SearchPath
.SearchSubFolders = False
.Filename = FileToSearch
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks

If .Execute() < 1 Then
MsgBox ("There were no files found.")
Exit Sub
End If


'loop through the files found in search path

For i = 1 To .FoundFiles.Count
ff = .FoundFiles(i)
Workbooks.Open ff
fn = ActiveWorkbook.Name

'determine the number of rows occupied and the week number
of the file just opened

anzColA = Workbooks(fn).Worksheets(1).Cells(Rows.Count,
"a").End(xlUp).Row
WeekNum = Mid(fn, 5, 2)

'loop through the rows in the current file

For k = 3 To anzColA
Set cls = Workbooks(fn).Worksheets(1).Range("a" +
CStr(k))
AnzConsA =
Workbooks("Consolidate.xls").Worksheets(1).Cells(Rows.Count,
"a").End(xlUp).Row + 1

'with the current store name loop through the rows in
the consolidate worksheet

For m = 4 To AnzConsA
Set cons =
Workbooks("Consolidate.xls").Worksheets(1).Range("a" + CStr(m))
If cons.Value = "" Or cons.Value = cls.Value Then

'if store was found add the sales figure to the
corresponding week
'and select only the day selected on the
consolidate worksheet

SelDay =
Workbooks("Consolidate.xls").Worksheets(1).Range("e1").Value
cons.Value = cls.Value
cons.Offset(0, WeekNum) = cons.Offset(0,
WeekNum) + cls.Offset(0, SelDay).Value
GoTo NextEntry
End If
Next m
NextEntry:
Next k

'close the current workbook

Workbooks(fn).Close SaveChanges:=False
Next i
End With

'Sort the consolidation rows

AnzConsA =
Workbooks("Consolidate.xls").Worksheets(1).Cells(Rows.Count,
"a").End(xlUp).Row + 1
Set cons = Workbooks("Consolidate.xls").Worksheets(1).Range("4:" +
CStr(AnzConsA))

'cons.Select
cons.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


End Sub

Hope it works at your end. It does here.

Regards

Hans