Forum Discussion
combining certain cells from several tabs in a work book resulting in a list
LizMF59 Glad you succeeded with the upload. But now you need to explain what I'm looking at. Which 22 pieces of information from the to 18 rows would you want to summarize?
Edit: I must say that the forms look somewhat chaotic with awkward formatting, lots of merged cell and information all-over-the place. That's not going to make it easier.
FYI when printed it "works" for the current situation. I am trying to upload the compiled data into an actual billing program.
LizMF59 That's up to you! I guess you need to invest time to learn Power Query or have someone write a macro for you (not me as I'm not into VBA) that picks up the correct cells and pastes them into at the push of a button.
Another option I'm just making up now is to create one row at the bottom of each sheet (should be part part of your MASTER template, say row 160, for new sheets) where 22 cells are linked to the ones you mentioned earlier from the first 15 rows. Then, you will have a row 160 on each sheet that you can easily copy/paste values to the billing sheet. The use PQ, which will then become much easier, by the way.
- Riny van Eeklen,
Again I thank you for your time and efforts.
How would you propose I move forward in utilizing
your efforts with my data? LizMF59 Attached file contains a very rough PowerQuery solution that extracts the information from the 22 cells in each sheet you identified. You can see what is done but you can't refresh the query unless you update the source step in the first query. Currently, it points to a file on my local hard drive.
Another thing is that it looks for all sheets with a name starting with "Sample". Your real file will not have that, of course. So, you'll have to make the sheets recognizable for PQ in some other way and use that in the step that filters the relevant sheets.
The end result is loaded into sheet "Merge1", where Name is the source sheet name, followed by 22 columns that probably need to be sorted in a more meaningful way.
The key to the whole process lays in a mapping table where the 22 items are identified by row and column number.
As said this is a very rough approach. It needs to be cleaned-up and made nicer. But that goes beyond the scope of this exercise. Just wanted to demonstrate that PQ can create something useful out of quite unstructured data that's "all-over-the-place".
- Thank you
LizMF59 Mapped those cell and it's quite a mess, if I may say so. Will get back to you later.