SOLVED
Home

How to automatically extract data from a monthly report to a dataframe

%3CLINGO-SUB%20id%3D%22lingo-sub-792880%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20extract%20data%20from%20a%20monthly%20report%20to%20a%20dataframe%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-792880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F388277%22%20target%3D%22_blank%22%3E%40davidsjk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20could%20upload%20your%20worksheet%20with%20some%20sample%20and%20realistic%20test%20data%2C%20that%20will%20help%20you%20get%20a%20much%20quicker%20response.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793615%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20extract%20data%20from%20a%20monthly%20report%20to%20a%20dataframe%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20suggestion!%3C%2FP%3E%3CP%3EI%20have%20upload%20a%20sample%20report%20and%20a%20very%20rough%20draft%20of%20what%20I%20imagine%20my%20database%20should%20look%20like.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-791454%22%20slang%3D%22en-US%22%3EHow%20to%20automatically%20extract%20data%20from%20a%20monthly%20report%20to%20a%20dataframe%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-791454%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20fairly%20new%20excel%20user%20on%20mac%2C%3C%2FP%3E%3CP%3EI%20receive%20monthly%20reports%20that%20all%20have%20the%20same%20format(but%20are%20not%20in%20a%20table).%3C%2FP%3E%3CP%3EI%20wanted%20to%20set%20up%20a%20data%20frame%26nbsp%3Bto%20automatically%20collect%20and%20store%20important%20data%20from%20these%20reports%20into%20a%20clean%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20able%20to%20copy%20these%20reports%20into%20one%20excel%20file%20as%20separate%20worksheets%2C%3C%2FP%3E%3CP%3Ebut%20I%20am%20unable%20to%20set%20up%20a%20system%20that%20would%20fill%20out%20each%20row%20with%20data%20from%20each%20worksheet(individual%20report).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20suggestions%20on%20how%20to%20set%20up%20this%20up%3F%3C%2FP%3E%3CP%3Eor%20idea%20on%20better%20ways%20to%20set%20up%20a%20dataframe%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20suggested%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%20I%20have%20attached%20a%20sample%20report%20along%20with%20a%20rough%20draft%20of%20the%20data%20frame.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-791454%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797662%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20extract%20data%20from%20a%20monthly%20report%20to%20a%20dataframe%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797662%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F388277%22%20target%3D%22_blank%22%3E%40davidsjk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20updated%20few%20cells%20in%20the%20data%20frame%20tab%20with%20a%20possible%20solution(%20which%20is%20only%20partial%20solution).%20I%20am%20assuming%20that%20you%20will%20have%20one%20row%20per%20month%20in%20Dataframe.%26nbsp%3B%20if%20yes%2C%20you%20can%20update%20the%20first%20column%20%22sheet%20name%22%26nbsp%3B%20-%20this%20is%20the%20tab%20where%20you%20will%20extract%20data.%26nbsp%3B%20the%20cell%20references%20need%20to%20be%20updated%20in%20the%20formule%20in%20B2%2C%20C2%2C%20D2%20etc%20(first%20row).%26nbsp%3B%20for%20example%20for%20country%20i%20assumed%20that%20L3%20cell%20in%202019JUN%20tab%26nbsp%3B%20is%20the%20source%2C%20hence%20the%20formula%20in%20B2%20is%20%3DIFERROR(INDIRECT(%24A2%26amp%3B%22!L3%22)%2C%22%22)%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%20complete%20this%2C%20you%20will%20have%20to%20populate%20the%201st%20row%2C%20with%20formula%20like%20in%20B2%20and%20C2%2C%20then%20copy%20%2Fpaste%20these%20formula%20to%20the%20next%20rows.%26nbsp%3B%20of%20course%2C%20the%20first%20column%20%22sheet%20name%22%20needs%20to%20be%20updated%20as%20well.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehope%20it%20helps%20!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797724%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20extract%20data%20from%20a%20monthly%20report%20to%20a%20dataframe%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797724%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much!%3C%2FP%3E%3CP%3EThis%20is%20a%20very%20elegant%20solution%2C%20and%20a%20useful%20tool%20to%20have!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYesterday%2C%20I%20had%20written%20a%20VBA%20to%20accomplished%20a%20similar%20task%2C%20but%20this%20method%20is%20easier%3C%2FP%3E%3CP%3Eand%20more%20convenient.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20what%20I%20did%20with%20the%20VBA.%3C%2FP%3E%3CP%3EI%20created%20a%20Variable%20input%20for%20the%20sheet%20name%2C%20and%20made%20the%20macros%3C%2FP%3E%3CP%3Ecopy%20the%20data%20onto%20the%20next%20open%20line.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Plugdata()%3CBR%20%2F%3E'%23Define%20Variables%3CBR%20%2F%3EDim%20Reportname%20As%20String%2C%20Country%20As%20String%2C%20Year%20As%20Integer%2C%20Month%20As%20String%2C%20Regular_EMP%20As%20Integer%3CBR%20%2F%3EReportname%20%3D%20InputBox(%22Type%20the%20Report%20Name%20eg.%202017Jan%22%2C%20%22Type%20Report%20Name%22%2C%20%22Type%20Report%20Name%20Here%22)%3CBR%20%2F%3EWorksheets(Reportname).Select%3C%2FP%3E%3CP%3ECountry%20%3D%20Range(%22L3%22)%3CBR%20%2F%3EYear%20%3D%20Range(%22H1%22)%3CBR%20%2F%3EMonth%20%3D%20Range(%22G1%22)%3CBR%20%2F%3ERegular_EMP%20%3D%20Range(%22D10%22)%3C%2FP%3E%3CP%3E'%23Select%20Paste%20Location%3CBR%20%2F%3EWorksheets(%22Data%22).Select%3CBR%20%2F%3EWorksheets(%22Data%22).Range(%22A1%22).Select%3CBR%20%2F%3EIf%20Worksheets(%22Data%22).Range(%22A1%22).Offset(1%2C%200)%20%26lt%3B%26gt%3B%20%22%22%20Then%3CBR%20%2F%3EWorksheets(%22Data%22).Range(%22A1%22).End(xlDown).Select%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3E'%23Enter%20Data%3CBR%20%2F%3EActiveCell.Offset(1%2C%200).Select%3CBR%20%2F%3EActiveCell.Value%20%3D%20Country%3CBR%20%2F%3EActiveCell.Offset(0%2C%201).Select%3CBR%20%2F%3EActiveCell.Value%20%3D%20Year%3CBR%20%2F%3EActiveCell.Offset(0%2C%201).Select%3CBR%20%2F%3EActiveCell.Value%20%3D%20Month%3CBR%20%2F%3EActiveCell.Offset(0%2C%201).Select%3CBR%20%2F%3EActiveCell.Value%20%3D%20Regular_EMP%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797746%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20extract%20data%20from%20a%20monthly%20report%20to%20a%20dataframe%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797746%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3BI%20was%20wondering%2C%20is%20there%20a%20way%20to%20extract%20data%20from%20a%20cell%20that's%20not%20always%20in%20the%20same%20location%3F%3C%2FP%3E%3CP%3EFor%20example%2C%20in%20the%20Sample%20report%2C%20the%20Expenditure%20can%20be%20found%20in%20D138%2C%20with%20the%20word%20%22Expenditure%22%20written%20right%20about%20it%20in%20D137.%20However%2C%20some%20reports%26nbsp%3Bcome%20back%20with%20extra%20lines%20added%20above%2C%20which%20moves%20the%20Expenditure%20to%20D1XX%20(e.g.%20D143).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20again%2C%20thank%20you%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799484%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20extract%20data%20from%20a%20monthly%20report%20to%20a%20dataframe%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F388277%22%20target%3D%22_blank%22%3E%40davidsjk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20following%20formula%20matches%20%22Expenditure%22%26nbsp%3B%20in%20column%20D.%20If%20it%20finds%20a%20match%2C%20returns%20the%20value%26nbsp%3B%20in%20the%20next%20row%20after%20it%20finds%20the%20first%20occurance%20of%20%22Expenditure%22.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(%202019JUN!D%3AD%2C%20MATCH(%22Expenditure%22%2C2019JUN!D%3AD)%2B1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20removes%20the%20hardcoding%20of%20Expenditure%20cell.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-801571%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20extract%20data%20from%20a%20monthly%20report%20to%20a%20dataframe%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-801571%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20looking%20all%20over%20for%20a%20formula%20like%20this.%3C%2FP%3E%3CP%3EWould%20it%20be%20possible%20to%20combine%20this%20with%20the%20previous%20formula%20to%20create%20a%20formula%20that%20can%20be%20adapted%20to%20various%20worksheets%20named%20in%20column%20A%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-801755%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20extract%20data%20from%20a%20monthly%20report%20to%20a%20dataframe%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-801755%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F388277%22%20target%3D%22_blank%22%3E%40davidsjk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efollowing%20is%20a%20combination%20of%20two.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(%20INDIRECT(%24A2%26amp%3B%22!D%3AD%22)%2C%20MATCH(%22Expenditure%22%2CINDIRECT(%24A2%26amp%3B%22!D%3AD%22))%2B1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20for%20Expenditure.%26nbsp%3B%20For%20other%20columns%2C%20you%20need%20to%20define%20similar%20rules%20(such%20as%20the%20row%20below%20%22Total%20Sponsonship%22%26nbsp%3B%20in%20column%20D%3AD).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805742%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20extract%20data%20from%20a%20monthly%20report%20to%20a%20dataframe%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805742%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3BThis%20is%20awesome!%20I%20will%20try%20using%20this%20to%20finish%20the%20database!%3C%2FP%3E%3CP%3EI%20will%20let%20you%20know%20how%20it%20goes.%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
davidsjk
Occasional Contributor

Hello,

 

I am a fairly new excel user on mac,

I receive monthly reports that all have the same format(but are not in a table).

I wanted to set up a data frame to automatically collect and store important data from these reports into a clean table.

 

I am able to copy these reports into one excel file as separate worksheets,

but I am unable to set up a system that would fill out each row with data from each worksheet(individual report).

 

Do you have any suggestions on how to set up this up?

or idea on better ways to set up a dataframe?

 

As suggested by @Kodipady  I have attached a sample report along with a rough draft of the data frame. 

 

Thanks in advance!

9 Replies

@davidsjk 

If you could upload your worksheet with some sample and realistic test data, that will help you get a much quicker response.  

@Kodipady 

Thank you for the suggestion!

I have upload a sample report and a very rough draft of what I imagine my database should look like.

Solution

@davidsjk 

I updated few cells in the data frame tab with a possible solution( which is only partial solution). I am assuming that you will have one row per month in Dataframe.  if yes, you can update the first column "sheet name"  - this is the tab where you will extract data.  the cell references need to be updated in the formule in B2, C2, D2 etc (first row).  for example for country i assumed that L3 cell in 2019JUN tab  is the source, hence the formula in B2 is =IFERROR(INDIRECT($A2&"!L3"),"")  

 

to complete this, you will have to populate the 1st row, with formula like in B2 and C2, then copy /paste these formula to the next rows.  of course, the first column "sheet name" needs to be updated as well.  

 

hope it helps !!

@Kodipady Thank you so much!

This is a very elegant solution, and a useful tool to have!

 

Yesterday, I had written a VBA to accomplished a similar task, but this method is easier

and more convenient.

Thanks!

 

Here is what I did with the VBA.

I created a Variable input for the sheet name, and made the macros

copy the data onto the next open line.

 

Private Sub Plugdata()
'#Define Variables
Dim Reportname As String, Country As String, Year As Integer, Month As String, Regular_EMP As Integer
Reportname = InputBox("Type the Report Name eg. 2017Jan", "Type Report Name", "Type Report Name Here")
Worksheets(Reportname).Select

Country = Range("L3")
Year = Range("H1")
Month = Range("G1")
Regular_EMP = Range("D10")

'#Select Paste Location
Worksheets("Data").Select
Worksheets("Data").Range("A1").Select
If Worksheets("Data").Range("A1").Offset(1, 0) <> "" Then
Worksheets("Data").Range("A1").End(xlDown).Select
End If

'#Enter Data
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Country
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Year
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Month
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Regular_EMP


End Sub

 

 

@Kodipady I was wondering, is there a way to extract data from a cell that's not always in the same location?

For example, in the Sample report, the Expenditure can be found in D138, with the word "Expenditure" written right about it in D137. However, some reports come back with extra lines added above, which moves the Expenditure to D1XX (e.g. D143).

 

Once again, thank you for your help!

@davidsjk 

The following formula matches "Expenditure"  in column D. If it finds a match, returns the value  in the next row after it finds the first occurance of "Expenditure".  

 

=INDEX( 2019JUN!D:D, MATCH("Expenditure",2019JUN!D:D)+1)

 

This removes the hardcoding of Expenditure cell.  

@Kodipady 

I've been looking all over for a formula like this.

Would it be possible to combine this with the previous formula to create a formula that can be adapted to various worksheets named in column A?

@davidsjk 

following is a combination of two. 

=INDEX( INDIRECT($A2&"!D:D"), MATCH("Expenditure",INDIRECT($A2&"!D:D"))+1)

 

This is for Expenditure.  For other columns, you need to define similar rules (such as the row below "Total Sponsonship"  in column D:D).

 

@Kodipady This is awesome! I will try using this to finish the database!

I will let you know how it goes.

Thank you!

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies