combining certain cells from several tabs in a work book resulting in a list

Copper Contributor

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

 

16 Replies

@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.

thank you for your response.
each tab is client specific with not just demographic data in the first 20 rows but then it references there are columns for their dates of service and the charges accordingly with drop downs. the summary I am wanting to pull will not reference the dates of service. we have 4 clinicians each having approximatley 35 clients in their file. the file will then be used to upload into a billing program. if I have to copy that many cells manually then I have to however I was hoping there is a macro or something to expediate the process.

@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. 

how do I attach the sample file?

@LizMF59 

If you don't see the option to Drag and drop or browse below the editor,

Riny_van_Eekelen_0-1657458725210.png

you can share a file via a link to OneDrive, Google docs or similar.

Thank You Riny - your time and attention is greatly appreciated!

I am sorry - I only want the information from the top 18 rows merged into a single listing

@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.

These are the cells in each sheet that I want to get: B1 C3 C5 H2 b7 M1 K8 L7 P8 Q7 U2 U4 W5 U7 X7 C11 C14 Q11 Q12 Q13 Q14 Q15

FYI when printed it "works" for the current situation. I am trying to upload the compiled data into an actual billing program.

@LizMF59 Mapped those  cell and it's quite a mess, if I may say so. Will get back to you later.

@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".

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 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.