Solved

Retrieve data from different workbooks in the same file with conditions...

Posted on 2011-09-20
19
195 Views
Last Modified: 2012-05-12
Hi,

I need to retrieve values from a lot of files which are saved in the same file C:\Users\Roberto\Desktop\TEST
All files are made from the same Template.
The range to "import" is always the same: A15:N259 in Sheet "RELLENAR".

Conditions:
1) Each row imported to the Summary Workbook must have in column A the value that is in Cell B3 in the target Workbook.
2) Blanks or Empty rows MUST not be exported.
3) For each row in the range A15:N259....When there are rows that have the same values in columns A,D and E, I would like to receive the SUM of Column N for all the Rows that match the three values.

Could you help ?

Thank you very much for your time.
Roberto.
0
Comment
Question by:Pabilio
  • 11
  • 8
19 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Can you post a sample workbook so that the result would be meaningful to you and the troubleshooting and building easier to us as sometimes a small charachter in a name or a capital can make the diffrence between a macro that works and one that doesn't. This would also avoid waiste time.
gowflow
0
 
LVL 5

Author Comment

by:Pabilio
Comment Utility
Hi Gowflow,

Attached is one of the files that I need to import to the summary workbook.
All the files are made from the same template
The language is Spanish but I don't think that this is an issue but If you need it I could translate a file for you.
After looking to what I worte, i could see that In order to avoid importing useless rows to the summary workbook if value in column N is "" or 0 it does not need to be imported.

Thank you for your time Gowflow,

Roberto.
SAMPLE-FILE.xls
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
what I'll do is build the macro in a new workbook that will geet the data imported. If you already have a destination workbook then it would be easier and avoid also time to have the macro built there so post it and I will do it for you
gowflow
0
 
LVL 5

Author Comment

by:Pabilio
Comment Utility
In the attached file in Sheet Hoja2 is the destination sheet of the destination workbook.

Thank you Gowflow.
BB-B-COSTOS-DE-MUEBLES-Y-DATOS-B.xls
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Tkss for the file will work on it
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Clarification please. You say:
3) For each row in the range A15:N259....When there are rows that have the same values in columns A,D and E, I would like to receive the SUM of Column N for all the Rows that match the three values.
>> Where would you like to have this Sum be put as in col N there is a formula and actually it is the value of col J so say you have 3 rows identical in A,D and E and each one in N would hv the value of J the last one would bear the sum ??? or you want this value to be put in a NEW oclumn like M for example ?? I think it would make more sence or else if you insit on N then we will need to remove the formulas in N and only keep the subtotal as then it would be confilcting to have diffrent formulas for the same column.
gowflow
0
 
LVL 5

Author Comment

by:Pabilio
Comment Utility
Hi Gowflow,

Just one note...is not "J" is "I" the column for the first rows in column N

N = I is only untill row 122 then in row 126 starts an If and left formula and then in row 211 N = to B column
What I did with N column is to be filled with the "real" value that I need to import... some values are square meters (When N = I) some are lineal meters or could be square meters (If and Left formula) and some values are just units (From row 211 to the end).

I do not need the formula to be exported... for me is fine just with the values in column N and/or the sum of the matched fields...
I f the solution comes adding a new column is OK for me too.

Thanks again Gowflow.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok fine can you pls post an other sample file to import so I could have more than 1 as in your original post you mention I need to import several files so I would do it that you select in 1 go multiple files and it would import all of them in your sheet all following each others.
gowflow
0
 
LVL 5

Author Comment

by:Pabilio
Comment Utility
Sure Glow...

Here are two of them...

Thank you for your work on this.
Roberto.
SAMPLE-FILE-2.xls
SAMPLE-FILE-3.xls
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Great working on it !!! just pls bear with me as you hv lots of issues that need to be tested blank rows that are not blank !!! (they contain formula but vallue is blank) etc...
gowflow
0
 
LVL 5

Author Comment

by:Pabilio
Comment Utility
You are right... I forgot the formulas retrieving "" as value...
I'll be here... Agazapado, esperando y listo para ayudarte en lo que pueda :-)
Regards,
Roberto.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I love spanish but ...
Agazapado, esperando y listo para ayudarte en lo que pueda :-)
No comprende !!!
gowflow
0
 
LVL 5

Author Comment

by:Pabilio
Comment Utility

Seized, waiting and ready to help as far as I can  :-)

0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Nice !! Keep hanging :)
Will get back to you asap
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Pabilio,
I have a question Now that values are imported and as you requested that col A = B3 of every sheet then we have new columns which are the following
Col A = B3 of every sheet
Col B = Col A of everyCol  sheet
Col C = Col B of every sheet
...
Col O = Col N of Every sheet

1) When you say: For each row in the range A15:N259....When there are rows that have the same values in columns A,D and E, I would like to receive the SUM of Column N for all the Rows that match the three values.
>>> Do you refer to the original sheet Columns or the Destination sheet column ?
a) if the original sheet then
Col A >> Col B = PRODUCTO
Col D >> Col E = MELAMINA
Col E >> Col F = GROSSOR En MM
Col N >> Col O = MEDIA FINAL (to add when A,D,E are the same)

or
b) if it is the destination sheet then
Col A = MEUBLE
Col D = ESTRUCTURA
Col E = MELAMINA
Col N = M2 PINTADOS (add values when A,D,E are the same)

Please advise which of a) or b) is to be concidered.

2) Is it ok to Sort the data when we import it so that all of A,D,E are grouped together and follows each other alphabetically ?? is this a problem ?

3) I noticed within the data imported after removing the blank rows we have the header that repeats but with diffrent values in Col B somtimes PRODUCTO somtimes CHAPA / CANTOS shall we delete these lines ? as they contain no values

4) Also I noticed like a header but in Col D ESTRUCTURA rows where there is only ESTRUCTURA in col D and nothing in the whole row or also CAVETAS / INTERNO shall we delete these lines too ??

Pls let me know. Sorry it is going too slow but I prefer to go slow but sure.
gowflow
0
 
LVL 5

Author Comment

by:Pabilio
Comment Utility
Hi Glowflow !!....

Nice to ear from you.


1) When you say: For each row in the range A15:N259....When there are rows that have the same values in columns A,D and E, I would like to receive the SUM of Column N for all the Rows that match the three values.
>>> Do you refer to the original sheet Columns or the Destination sheet column ?
a) if the original sheet then
Col A >> Col B = PRODUCTO
Col D >> Col E = MELAMINA
Col E >> Col F = GROSSOR En MM
Col N >> Col O = MEDIA FINAL (to add when A,D,E are the same)

or
b) if it is the destination sheet then
Col A = MUEBLE
Col D = ESTRUCTURA
Col E = MELAMINA
Col N = M2 PINTADOS (add values when A,D,E are the same)

Please advise which of a) or b) is to be concidered.

ANSWER:
The columns I said are refered to the Original Workbooks...
When a row have the same "PRODUCTO", "MELAMINA" and "GROSOR en mm" then I would like to receive the sum of "MEDIDA FINAL"...
The other fields differents from the four named before there is not need to be exported...
MEDIDA FINAL means Final Measure...that's the quantity expresed in meters, square meters, units and so on... this is the only field that I need to know the sum of it.


2) Is it ok to Sort the data when we import it so that all of A,D,E are grouped together and follows each other alphabetically ?? is this a problem ?
ANSWER: Not at all.... indeed it will look better for the final result if the data is previously sorted

3) I noticed within the data imported after removing the blank rows we have the header that repeats but with diffrent values in Col B somtimes PRODUCTO somtimes CHAPA / CANTOS shall we delete these lines ? as they contain no values
ANSWER: If you can delete all headers with the macro it will be great !!...

4) Also I noticed like a header but in Col D ESTRUCTURA rows where there is only ESTRUCTURA in col D and nothing in the whole row or also CAVETAS / INTERNO shall we delete these lines too ??
ANSWER: This is only an identification tag for the furniture structure... there is not need to import it to the summary worksheet.

Thank you so much for your work in this question Gowflow...
I really appreciate it.

Regards,
Roberto.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
Pabilio,
Here it is. Please check this file to see if this is what you want. Here is how it works:

1) Make sure your macro settings are set to Medium to allow macro to run. If your not familiar start blank Excel choose from the menu Tools/Macros/Security make sure Medium is ticked and in the Trusted publishers both options Trust Installed add-ins and Trust Access to Visual Basic Projects are both ticked.
2) Download this version and run it and when prompt choose Enable Macroes.
3) in Hoja2 Sheet you will see in Cell A1 2 buttons: Import RELLENAR and Group/Sort SUM
4) You start first by activating Import RELLENAR here it will prompt you to choose a file to Import and will default to the current location of the workbook you can select any directory even network directory and you can select 1 file or multiple files to select multiple files click on the first file press SHIFT and will holding shift click on the last file it will highlight all files to Import. Press OK and you will see the Import.
5) Once Data imported just scrool there to make sure all is ok (you can do at this stage a screening to ensure all data rows are there and all non essecial rows have been deleted.
6) You can now activate the second button Group/Sort SUM and once done always you have a message box telling you it has completed. Check that the data is grouped and summed to your satissfaction.

Please feel free to comment and let me know if my understanding was not correct and need corrections.
gowflow
BB-B-COSTOS-DE-MUEBLES-Y-DATOS-B.xls
0
 
LVL 5

Author Closing Comment

by:Pabilio
Comment Utility
Thank you very much Gowflow...
Excellent piece of work.
I liked the Sort and Subtotal solution you gave to the problem.
Regards,
Roberto.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
My pleasure !!! Am happy I could help. Pls feel free to ask anytime. If you have something else on an other question shortly pls post it here so I could help you.
gowflow
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now