Jul 07 2022 08:24 PM
I have several tabs formated the same
I want to copy certain cells in each of the tabs to another tab resulting in a list of the names in column A, birthdates in columns B, addresses in column C etc I have 22 cells to get information from to go into 22 columns
There's got to be a way to copy the cells from each sheet tab into a "master" tab creating a "database listing" of demographic data and other information
Thank you in advance
Liz
Jul 07 2022 09:24 PM
@LizMF59 If it's a one time exercise to create the list, I would just copy/paste the data from all sheets into a single sheet, one below the other without repeating the headers. Then just delete the columns you don't want.
Having said that, I wonder why you even bothered to create separate sheets to begin with. If there is a compelling reason to keep the data in separate sheets, and if the data is changing frequently you could consider Power Query to combine the sheets.
Jul 08 2022 04:13 AM
Jul 08 2022 04:18 AM
@LizMF59 Understand! Macros is not my cup of tea. I'd go for Power Query, but can't explain exactly how without seeing the file. Or at least an anonymized version of it.
Jul 10 2022 05:42 AM
will 5 people be enough of a sample?@Riny_van_Eekelen
Jul 10 2022 05:49 AM
Jul 10 2022 06:12 AM
If you don't see the option to Drag and drop or browse below the editor,
you can share a file via a link to OneDrive, Google docs or similar.
Jul 10 2022 06:19 AM
Jul 10 2022 06:23 AM
Jul 10 2022 06:24 AM
Jul 10 2022 06:36 AM - edited Jul 10 2022 06:44 AM
@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.
Jul 10 2022 07:08 AM
Jul 10 2022 07:27 AM - edited Jul 10 2022 07:39 AM
@LizMF59 Mapped those cell and it's quite a mess, if I may say so. Will get back to you later.
Jul 10 2022 07:53 PM
Jul 10 2022 09:20 PM
@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".
Jul 12 2022 07:31 PM
Jul 12 2022 09:15 PM
@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.