I'm trying to combine multiple tables (1 in each tab) from the same worksheet.
When I do, it's tethering the names from 1 list under the other so it look like the following
Baba Ali
- James Acaster*
- James Adomian*
- Jayde Adams
- John Aboud*
- Pamela Adlon*
- Raymond Ablack*
- Rose Abdoo*
Ben Bailey
- James Acaster*
- James Adomian*
- Jayde Adams
- John Aboud*
- Pamela Adlon*
- Raymond Ablack*
- Rose Abdoo*
Instead of not duplicating the entries and listing all the names into a single column.
Last edited by kyber; 10-13-2021 at 04:18 PM.
Use Power Query instead to combine the 2 tables.
M Code:
Please Login or Register to view this content.
@kyber;
Since you tried using Pivot Table for listing unique counselors, i used the built-in Pivot Table Wizard function to solve the problem in sheet "Merged" of the attached file.
Do not forget to refresh the pivot table.
Note: If you only need the list of the counselors, the uncheck "Value" in the "Choose Fields" section in the "Field List"
Last edited by Haluk; 10-09-2021 at 03:48 AM.
@Haluk this is exactly what I'm looking for, however, I'm a bit confused on where the data source of the Pivot Table is? And I have no idea how to merge the two.
@josephteh I have quickly learned how to do the power query, however, to do this for 20 individual locations is a bit labor intensive so I was hoping there might be an easier way.
Or perhaps if I can merge the tables by means of using an array formula that might do the trick too, but I'm not capable of that. I am struggling importing 1 table yet alone two. Here is what I have so far.
=IFERROR(INDEX(Table1[COUNSELOR],MATCH(0,COUNTIF($A2:A$2,Table1[COUNSELOR]&"")+IF(Table1[COUNSELOR]="",1,0),0)),"")
By merging it once for each location it will auto import every time I need to do a monthly update.
@josephteh disregard my inquiry. I just learned that you can manually update power queries. My pivot table is now functional. Thank you.
@kyber, i've prepared an animation for you showing how to activate the Pivot Table Wizard and solve your problem.
https://drive.google.com/file/d/1hdb...ew?usp=sharing
Glad you sort it out, kyber! Do try out the PivotTable Wizard suggested by Haluk. Instead of selecting the ranges, you could type in the Table Names.
Thank you both!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks