• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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

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
Pabilio
Asked:
Pabilio
  • 11
  • 8
1 Solution
 
gowflowCommented:
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
 
PabilioAuthor Commented:
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
 
gowflowCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
PabilioAuthor Commented:
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
 
gowflowCommented:
Tkss for the file will work on it
gowflow
0
 
gowflowCommented:
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
 
PabilioAuthor Commented:
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
 
gowflowCommented:
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
 
PabilioAuthor Commented:
Sure Glow...

Here are two of them...

Thank you for your work on this.
Roberto.
SAMPLE-FILE-2.xls
SAMPLE-FILE-3.xls
0
 
gowflowCommented:
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
 
PabilioAuthor Commented:
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
 
gowflowCommented:
I love spanish but ...
Agazapado, esperando y listo para ayudarte en lo que pueda :-)
No comprende !!!
gowflow
0
 
PabilioAuthor Commented:

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

0
 
gowflowCommented:
Nice !! Keep hanging :)
Will get back to you asap
gowflow
0
 
gowflowCommented:
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
 
PabilioAuthor Commented:
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
 
gowflowCommented:
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
 
PabilioAuthor Commented:
Thank you very much Gowflow...
Excellent piece of work.
I liked the Sort and Subtotal solution you gave to the problem.
Regards,
Roberto.
0
 
gowflowCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now