asked on Extract Data and filename Macro
In a folder I have multiple files all with the same format
In each file i have a tab called "statement"
data have to be pulled from B,C,D,E,F and G columns
Steps:
once file directory is selected
from all the files of statement tab
Copy Rows of B,C,D,E,F and G rows which is not 0.00 or copy all with values and 0.00 can be deleted in output file
In an output file copy file name and paste as values what has been copied in the above step
please let me know if I am not clear.
attached input file and sample output in the output tab
I Could see some help in below sites but do not know how put across
stackoverflow.com/question
ashishmathur.com/extract-d
superuser.com/questions/44
Statment.xlsx
In each file i have a tab called "statement"
data have to be pulled from B,C,D,E,F and G columns
Steps:
once file directory is selected
from all the files of statement tab
Copy Rows of B,C,D,E,F and G rows which is not 0.00 or copy all with values and 0.00 can be deleted in output file
In an output file copy file name and paste as values what has been copied in the above step
please let me know if I am not clear.
attached input file and sample output in the output tab
I Could see some help in below sites but do not know how put across
stackoverflow.com/question
ashishmathur.com/extract-d
superuser.com/questions/44
Statment.xlsx
.NET ProgrammingVisual Basic ClassicMicrosoft ExcelVB Script
Last Comment
Subodh Tiwari (Neeraj)
in your sample workbook, there isn't much difference in the B:G cell data, relative to zero values. What am I missing?
ASKER
I get this file every 15 days and the values in currency and combination changes for example i might receive values in AUD_SEK; GBP_SEK; HKD_SEK; NOK_SEK; and SGD_SEK; NOK_SEK; PLN_SEK in other fortnight
while cell references are remain standard/same the values will be in different cells
and what i have provided here is the sample data. hope i am clear and if you are referring to the output file and file two sorry its my mistake the amount and currency might have changed
while cell references are remain standard/same the values will be in different cells
and what i have provided here is the sample data. hope i am clear and if you are referring to the output file and file two sorry its my mistake the amount and currency might have changed
I'm a pretty visual person. Please post a workbook or two that shows me the expected input and expected output.
ASKER
Sure. in the attached file i have two tabs as input (2) and Input (3) where i have some number in income, cost, receivables and liabilities. what i would need is where ever the numbers are there for each of those categories (income, cost etc.,) have to be pulled into a sheet. please not input files are different workbooks in a directory
please do let me know if i am not clear still
Statment.xlsx
please do let me know if i am not clear still
Statment.xlsx
Will these input files have exclusive categorical non-zero values?
If you have posted representative data, then I can expect that no two (or more) workbooks will ever have a non-zero value in any given category, such as I see in cell D12.
If you have posted representative data, then I can expect that no two (or more) workbooks will ever have a non-zero value in any given category, such as I see in cell D12.
ASKER
If I understand correctly, it can be any combination of data. i will have more than 10 workbooks and some might have value in one category and might be zero in others
The only thing that i am looking is
File name:
Category (example income/revenue etc.,)
Currency (example EUR/SEK)
local and other currency
The only thing that i am looking is
File name:
Category (example income/revenue etc.,)
Currency (example EUR/SEK)
local and other currency
1. Do these values need to be summed?
2. Are all workbooks in the same folder?
2. Are all workbooks in the same folder?
ASKER
no these values need not be summed and yes all files in the same folder
these values need not be summedThen how do we treat the non-zero data in light of
any combination of dataIf not summed, then options are
* first value wins
* last value wins
* random value wins (three or more scenario)
* error message
ASKER
I need the data to be copy pasted in each row this a a consolidation report
here is how it works
i have folder named "Analysis"
Analysis
|->File 1
|->File 2
|->File 3
In File 1 sheet "statement" tab I have Income ,Costs, Receivables, Liabilities in col B, Currency combination in Col C (example) AUD_SEK and amount in column D:G
output will Category from Col B, Currency from Col C, and amounts from Col D:G
if it takes log time to pick only non zeros even if it can consolidate all files and only tab statmen to one sheet that also will be fine
"
here is how it works
i have folder named "Analysis"
Analysis
|->File 1
|->File 2
|->File 3
In File 1 sheet "statement" tab I have Income ,Costs, Receivables, Liabilities in col B, Currency combination in Col C (example) AUD_SEK and amount in column D:G
output will Category from Col B, Currency from Col C, and amounts from Col D:G
if it takes log time to pick only non zeros even if it can consolidate all files and only tab statmen to one sheet that also will be fine
"
Picking the non-zero values isn't the issue. The issue is what to do if a cell has non-zero value in multiple workbooks.
For example, Workbook: File 1.xlsx, worksheet: Input, Cell D12 = 1234.56
Workbook: File 2.xlsx, worksheet: Input, Cell D12 = 789.00
What should the D12 cell value be in the output?
For example, Workbook: File 1.xlsx, worksheet: Input, Cell D12 = 1234.56
Workbook: File 2.xlsx, worksheet: Input, Cell D12 = 789.00
What should the D12 cell value be in the output?
ASKER
those will come in different columns of the out put file
say Workbook: File 1.xlsx, worksheet: Input, Cell D12 = 1234.56 will come in Column D
and Workbook: File 2.xlsx, worksheet: Input, Cell D12 = 789.00 will come in Column E
say Workbook: File 1.xlsx, worksheet: Input, Cell D12 = 1234.56 will come in Column D
and Workbook: File 2.xlsx, worksheet: Input, Cell D12 = 789.00 will come in Column E
You've completely lost me. I haven't a clue what you are trying to do.
ASKER
its a simple thing dont know whats so complicated
get a data from different files into a single file by copy pasting values
get a data from different files into a single file by copy pasting values
At this point, I recommend you click the request attention link and ask for more expert involvement. I won't get to visit this question until after the weekend.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
It worked brilliant. my files have some merged cells and formulas how can unmerge all files and paste specials as values in the result file.
You are a genius.
You are a genius.
Thanks for the feedback.
Can you upload one such sample file?
Can you upload one such sample file?
ASKER
Simply Genius.Thank you so much
You're welcome Uday! Glad I could help. :)
Thanks for the feedback.
Thanks for the feedback.