Solution to pull data

Question

Solution to pull data

Matthew 61

Hi, I need help to pull data.

There is existing data from four worksheet: Illnois Medical, Illnois Food, Florida Medical, and Florida Food.

There are blank worksheet: Dog Medical, Dog Food, Cat Medical, Cat Food.

I would like for the blank worksheet to pull data the columns from the existing data.

Matthew

Answer accepted by question author

2 additional answers

Your answer

Answer 1

@MDN5551

WOW.... In sumarry:
1/ Format data in sheets Illinois Med, Illinois Food, Florida Med and Florida Food as Tables
2/ Rename them respectively IllinoisMed, IllinoisFood, FloridaMed and FloridaFood
3/ Click somewhere in the IllinoisMed table > Data (tab) > From Table/Range (Power Query editor opens) > Arrow below Close & Load > Close & Load To... > Check "Only Create Connection" > OK
4/ Repeat #3 for tables IllinoisFood, FloridaMed and FloridaFood
5/ In the Data tab > Queries & Connections (the corresponding pane appears on the right)
6/ Right-click on query IllinoisMed > Append > Second table = FloridaMed > OK > Arrow below Close & Load > Close & Load To... > Check "Only Create Connection" > OK + In the Query & Connection pane > Right-click on this new query > Rename > Medical
7/ Repeat #6 with query IllinoisFood > Second table = FloridaFood + Rename this new query Food
8/ In the Query & Connection pane > Right-click on query Medical > Reference (Power Query editor opens) > Click the arrow on the [Pet] column > Uncheck "Cat" > OK > Close & Load (this creates a new sheet with table for Medical + Dog)
9/ Repeat #8 but uncheck "Dog" this time => new sheet with table for Medical + Cat
10/ In the Query & Connection pane > Right-click on query Food > Reference (Power Query editor opens) > Click the arrow on the [Pet] column > Uncheck "Cat" > OK > Close & Load (this creates a new sheet with table for Food + Dog)
11/ Repeat #10 but uncheck "Dog" this time => new sheet with table for Food + Cat

Matthew 61 Reputation points

2021-01-13T17:31:12.673+00:00

It works and very helpful. Now, I understand how to get there. Thank you very much!!
Lz._ 38,106 Reputation points Volunteer Moderator

2021-01-14T07:06:43.31+00:00

@Matthew . Glad I could help & Thanks for posting back
In this PDF a number of pointers in case you'll want to know more about Get & Transform/Power Query
MDN5551 21 Reputation points

2021-01-19T23:44:55.65+00:00

That's so awesome. Thank you for the PDF. Very resourceful. Have a great week!

Answer 2

@Matthew

Maybe you could try as below.

Insert a Table for the data in worksheet IIInois Medical.
Filter Dog in the list.
Then the data are listed as below automatically, copy A2:E5.
Paste in cell A3 in worksheet Dog Medical.

Other steps are similar.

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Lz._ 38,106 Volunteer Moderator

Hi @Matthew

With Get & Transform aka Power Query (no copy/paste nor anything like that). Sample available here.

NB: When you add/change data in the Source tables (Illinois medical, Illinois food...), right-click somewhere in the green tables (Dog medical, Cat food...) > Refresh

MDN5551 21 Reputation points

2021-01-11T22:00:11.197+00:00

I really like your solution. I am still strugging to understand instruction from "Get & Transform" instruction. I am unable to right-click somewhere in the green tables and click Refresh. I think because I only have read only access.

My request: can you provide instruction on how you got it using the Power Query. That would be helpful.

Matthew
Lz._ 38,106 Reputation points Volunteer Moderator

2021-01-12T06:26:02.767+00:00

Hi @MDN5551

Not sure I understand... The file I shared isn't protected and if you downloaded it you should do whatever you want with it

Could you confirm that when right-clicking in the green table on tab Dog Med you get a Refresh option as shown below?
MDN5551 21 Reputation points

2021-01-12T13:46:50.67+00:00

Thanks.

Can you provide me snapshot or write instruction on how you create it on Power Query?

Share via

Solution to pull data

2 additional answers

Your answer