Summary Selective Data From Various File In One File

SAN04 · Feb 11, 2023

I WANT TO SUMMARY SELECTIVE DATA in attached sheet (Summary) from various excel files attached (file 1 & 2) automatically.

Right now used only 2 files (attached) from where data is to be picked in summary.

However, need automation as data from such 138 files need to be summarized.

Please help.

Moderator note: Avoid shouting.

SAN04 · Feb 11, 2023

Please help

Marc L · Feb 11, 2023

Please elaborate without nothing to guess ...

SAN04 · Feb 12, 2023

Thanks for the response.

The data which i have incorporated in Summary is from file 1 and file 2 but it is done manually by me.

I need help for the automation of the same means the data in "summary" file should be update automatically from various files under the headers (in colors) and date wise from file 1 and 2.

Also the formula to be such that in case in future the input sheet increases from 2 to 150 should be capable to fetch data.

vletm · Feb 12, 2023

SAN04
Is there any specific reason ...
Why Summary.xlsx and 2.xlsx looks same?

SAN04 · Feb 12, 2023

Ignore "tab 2" in the "sheet 2"

SAN04 · Feb 13, 2023

Hello sir,

Any update.

Marc L · Feb 13, 2023

Any explanation …

SAN04 · Feb 13, 2023

The data which i have incorporated in Summary is from file 1 and file 2 but it is done manually by me.

I need help for the automation of the same means the data in "summary" file should be update automatically from various files under the headers (in colors) and date wise from file 1 and 2.

Also the formula to be such that in case in future the input sheet increases from 2 to 150 should be capable to fetch data.

vletm · Feb 13, 2023

SAN04
Please, reread Your own #4 reply.
It looks almost same as Your #9 reply.

SAN04 · Feb 14, 2023

vletm said:
SAN04
Is there any specific reason ...
Why Summary.xlsx and 2.xlsx looks same?

By mistake i forget to delete Summary from sheet 2. Please ignore summary of File 2 (in new sheets sent to you i have deleted it)

Marc L · Feb 14, 2023

Update your initial post with a corrected attachment at least then create a new post to warn for this new attachment and

add all the necessary details we won't guess or you will have to fit yourself any help for what your forgot / misexplained …

SAN04 · Feb 15, 2023

SAN04 said:
I WANT TO SUMMARY SELECTIVE DATA in attached sheet (Summary) from various excel files attached (file 1 & 2) automatically.

Right now used only 2 files (attached) from where data is to be picked in summary.

However, need automation as data from such 138 files need to be summarized.

Please help.

Moderator note: Avoid shouting.

Thanks for the responses. Just a correction (mainly in file 2) from my side.

Trying to elaborate query and attaching correct files.

I have attached again two files (File 1 & 2) along with the "summary" sheet.

Requirement is to update the red highlighted description as per the progress upto cell "date" from file 1 & 2 (highlighted in Green) under various headers in Summary sheet from file 1 & 2 automatically - currently i have update summary sheet using copy paste as highlighted in red.

Also right now only 2 such files (file 1 & 2) but in future the files could be 200.

So, I need some program which once run on folder level could fetch and combine data from various file in one summary sheet in required Summary sheet format.

Sorry for the inconvenience.

Thanks!

Marc L · Feb 15, 2023

At first glance :

'cause of the source workbooks design the fast way can't be directly used and
as this is a VBA forum to learn the basics so would go for the classic slowest way …
I can't stand your last Summary workbook is really the expected result !

SAN04 · Feb 15, 2023

The red highlighted in summary book are the required/expected results.

Just incorporated manually for demonstration purpose.

Thanks

Marc L · Feb 16, 2023

Previous post VBA procedure (could be optimized without any useless Activate neither Select) for people interested for :

Code:

Sub compile()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

winmaster = ActiveWorkbook.Name
winpath = ActiveWorkbook.Path
dsource = winpath & "\source\"

    Sheets("master").Visible = True
    Sheets("master").Range("K:K").ClearContents
    Sheets("master").Activate
    Sheets("master").Range("B7").Value = 0
    Sheets("summary").Activate
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).ClearContents
    Sheets("master").Activate
   
Set oFSO = CreateObject("scripting.filesystemobject")
Set ofolder = oFSO.getfolder(dsource)

For Each ofile In ofolder.Files
    Cells(i + 1, 11) = ofile.Name
    i = i + 1

Next ofile

Do
counter = Sheets("master").Range("B7").Value
countermax = Sheets("master").Range("B8").Value
opfile = Sheets("master").Range("B9").Value
opfile = Sheets("master").Range(opfile).Value
opfile1 = dsource & opfile

    Workbooks.Open Filename:=opfile1

daterep = Range("AC2").Value

    Cells.Find(What:="3. ACTIVITIES", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

    Selection.UnMerge
pointrow1 = ActiveCell.Row
rangerow1 = "B" & pointrow1
rangerow2 = "P" & pointrow1
rangerow3 = "AC" & pointrow1

    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.End(xlUp).Select
eH1row = ActiveCell.Row
    Windows(winmaster).Activate
    Sheets("master").Range("B1").Value = "C22:C" & eH1row
   
    Windows(opfile).Activate

    Range(rangerow2).Select
    Selection.End(xlUp).Select
eH2row = ActiveCell.Row
    Windows(winmaster).Activate
    Sheets("master").Range("B2").Value = "P22:P" & eH2row

    Windows(opfile).Activate
    Range(rangerow3).Select
    Selection.End(xlUp).Select
eH3row = ActiveCell.Row
    Windows(winmaster).Activate
    Sheets("master").Range("B3").Value = "AC22:AC" & eH3row
   
    Windows(opfile).Activate
    Cells.Find(What:="5. AREA", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

    Selection.UnMerge

    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.End(xlDown).Select
AoC1row = ActiveCell.Row
    Selection.End(xlDown).Select
AoC2row = ActiveCell.Row

    Windows(winmaster).Activate
    Sheets("master").Range("B4").Value = "C" & AoC1row & ":C" & AoC2row

    Sheets("summary").Activate
    Range("C1048576").Select
    Selection.End(xlUp).Select
crow1 = ActiveCell.Row + 1

    Range("D1048576").Select
    Selection.End(xlUp).Select
crow2 = ActiveCell.Row + 1

    Range("E1048576").Select
    Selection.End(xlUp).Select
crow3 = ActiveCell.Row + 1

    Range("F1048576").Select
    Selection.End(xlUp).Select
crow4 = ActiveCell.Row + 1

    Sheets("master").Range("C6").Value = crow1
    Sheets("master").Range("D6").Value = crow2
    Sheets("master").Range("E6").Value = crow3
    Sheets("master").Range("F6").Value = crow4


rowpaste1 = "A" & Sheets("master").Range("B6").Value
rowpaste2 = "B" & Sheets("master").Range("B6").Value
rowpaste3 = "C" & Sheets("master").Range("B6").Value
rowpaste4 = "D" & Sheets("master").Range("B6").Value
rowpaste5 = "E" & Sheets("master").Range("B6").Value
rowpaste6 = "F" & Sheets("master").Range("B6").Value

high1val = Sheets("master").Range("C1").Value
high2val = Sheets("master").Range("C2").Value
high3val = Sheets("master").Range("C3").Value
concern = Sheets("master").Range("C4").Value

    Sheets("summary").Activate
    Range(rowpaste1).Value = opfile
    Range(rowpaste2).Value = daterep

    Windows(opfile).Activate
    If high1val = "skip" Then
    Else
        Range(high1val).Copy
        Windows(winmaster).Activate
        Range(rowpaste3).Select
        Selection.PasteSpecial Paste:=xlPasteValues
    End If
   
    Windows(opfile).Activate
    If high2val = "skip" Then
    Else
        Range(high2val).Copy
        Windows(winmaster).Activate
        Range(rowpaste4).Select
        Selection.PasteSpecial Paste:=xlPasteValues
    End If

    Windows(opfile).Activate
    If high3val = "skip" Then
    Else
        Range(high3val).Copy
        Windows(winmaster).Activate
        Range(rowpaste5).Select
        Selection.PasteSpecial Paste:=xlPasteValues
    End If

    Windows(opfile).Activate
    If concern = "skip" Then
    Else
        Range(concern).Copy
        Windows(winmaster).Activate
        Range(rowpaste6).Select
        Selection.PasteSpecial Paste:=xlPasteValues
    End If

    Windows(opfile).Activate
    ActiveWorkbook.Close savechanges:=False

    Sheets("master").Range("B7").Value = counter + 1
counter = Sheets("master").Range("B7").Value

Loop Until counter = countermax
   
    Sheets("master").Range("B7").Value = 0
    Sheets("master").Visible = False

End Sub

Marc L · Feb 16, 2023

According to post #13 attachment an Excel basics starter VBA demonstration (v2)

to paste to the Sheet1 worksheet module of Summary workbook, save it as binary format .xlsb

and move it to the folder containing only the source .xlsx workbooks

- if already in the same folder so once the Summary.xlsb workbook is created then delete the original Summary.xlsx workbook -

(or just update the variable P within this VBA procedure) :

Code:

Sub Demo1()
    Dim P$, F$, R&, V, L&
        P = ThisWorkbook.Path & "\"
        UsedRange.Offset(2).Clear
        Application.ScreenUpdating = False
        F = Dir$(P & "*.xlsx")
  While F > ""
    With Workbooks.Open(P & F, 0).ActiveSheet
        R = UsedRange.Rows.Count + 1
        Cells(R, 1).NumberFormat = "m/d/yyyy"
        Cells(R, 1) = .[AC2]
        V = Application.Match("3. ACTIVITIES PLANNED FOR NEXT DAY", .Columns(2), 0)
    If IsNumeric(V) Then
        L = V - 22
        Cells(R, 2).Resize(L, 3) = Application.Index(.[C22].Resize(L, 27), Evaluate("ROW(1:" & L & ")"), [{1,14,27}])
    End If
        V = Application.Match("5. AREA OF CONCERN", .Columns(2), 0)
    If IsNumeric(V) Then
        L = .Cells(.Rows.Count, 3).End(xlUp).Row - V
        If L > 0 Then Cells(R, 5).Resize(L) = .Cells(V + 1, 3).Resize(L).Value
    End If
       .Parent.Close False
    End With
        F = Dir$
  Wend
        Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !

SAN04 · Feb 17, 2023

Mark,

Please tell how should i use the program (Should i copy paste the program in excel sheet) and keep it in same folder where files are kept.
Followed by "Run".

Marc L · Feb 17, 2023

SAN04 said:
Marc,

No as yet explained in my previous post, just read - well - and follow …

Another way for bad readers : right click on the sheet tab then choose Code.

According to your post #13 attachment, both data workbooks do not use exactly the same layout neither the same worksheet

so that eliminates directly the fastest way which does not need to open each workbook under Excel.

Just creating a beginner level Excel template in order all data workbooks use the same worksheet with exactly the same layout

then faster ways will become doable …

SAN04 · Feb 18, 2023

Thanks Marc.

I have such 200 workbooks (none of them with same layout) received from vendor. Now i have to summarize the data (selected one) for in the "Summary" layout.

Hence needed the help.

Thanks again.

SAN04 · Feb 18, 2023

Marc,

As per your Post#18,

1) i have saved "Summary" file in .xlsb (Binary format)
2) Copied the code in your post#18
3) Pasted in "Visual basic" in "Summary" tab
4) Changed path - Inserted the folder name --- P = Source.Path & "\"
5) Ran the macro

Data came as required but only for "Sheet1"

Please help

Marc L · Feb 18, 2023

As I wrote « According to post #13 attachment » and as it works as expected on my side so the bad is on yours !

Did you try at least with only exactly the same attachment ?

Do you have the same issue with post #16 ?

SAN04 · Feb 19, 2023

As per post#16, the data is getting summarized but as you are aware that the layout is different in all sheets the same data is not getting captured

Marc L · Feb 19, 2023

As both proposals were made on your 'explanation' and your post #13 attachment so

without any clear answer to my questions that's a guessing challenge - as guessing can't be coding ! -

and as we are very beginners to guess anything, as any Excel forum is not a mind readers forum …

As starter you have to fit any proposal to what you forgot / misexplained.

So weird Excel is used for that kind of stuff, such issue can't occur under a database software

which can be 100 times faster to consolidate data !

When using Excel the best way is to use an unique template with a never changing layout with an unique worksheet

- or an unique worksheet name whatever -, so easy (but slower than a database software),

as 'automation' requires 'standardization' …

SAN04 · Feb 19, 2023

Understood Marcs,

But the data is given to me by my vendors as as dump. Now i am struggling it analyze the data.

Hence requested the help from all the persons here.

Let us see if some solution.

Thanks Again!

Summary Selective Data From Various File In One File

Member

Attachments

Member

Excel Ninja

Member

Excel Ninja

Member

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Attachments

Excel Ninja

Member

Excel Ninja

Excel Ninja

Member

Excel Ninja

Member

Member

Excel Ninja

Member

Excel Ninja

Member