•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Is there a more efficient way to do what I am doing? (Merge tables keeping values)
Our take
Are you seeking a more efficient method to merge multiple tables while retaining unique identifiers and associated values? In your case, you have three tables with names and corresponding values, and you want to consolidate them into a single table that highlights these relationships. While your current approach using Power Query and pivot tables is functional, it's understandable to desire a more streamlined solution. Let's explore innovative techniques that can simplify this process, enhance your productivity, and make data comparison more intuitive.
So I have three tables:
And I want to merge them into a table like this one:
As you can see, I want to keep the first column as the unique list of names, adding all the different names from the 3 columns. But I want to grab the 3 values from the different tables. I know the values in the different columns don't match the names, but that is exactly the point since I want to compare the values fro the different names. I don't mind the empty cells either.
So far what I am doing is append the tables into a single one with power query, loading it into a pivot table to stack de names, then adding the three different values and copy paste it without format into a different sheet and then converting it to an Excel table.
It actually works, but I can't stop thinking that this is way too innefficient and annoying and that there is an easier way to do it. Can anyone help with this?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Two tables/two workbooks. Matching data in columns to add data to a third.I have two different tables on two workbooks. Table 1, Workbook 1: Column 1 is names, Column 2, initials, Column 3 dates. Table 2, Workbook 2: Column 1 initials, Column 2 dates. There is more data on the tables that isn’t relevant to my question and not all names, initials, and dates are common to both tables. Is there a way to match dates and initials on both tables (Columns 1 and 2) that will add the names to a Column 3 on Table 2, Workbook 2? Right now I’m eyeballing dates and initials and cutting and pasting. submitted by /u/TheCliff977 [link] [comments]
- How do I Maximize File EfficiencyI work with data sets that I typically look at forecast by year. Currently when I look at 2026 and 2027 it is rougly 1.4M lines of data. I have to put these in two separate data pulls and tables. Then I have six different customers included in this data. so I have to create 6 tabs with six diffrent pivot tables for them to look at. This has created a massive file that lags just to open, save or close so I really have two questions and am open to suggestions. Would it be better to store the data in one worksheet and then link a second worksheet that just has the pivot tables and separated look? If so how would I creat that link? Can you explain to me like I am 5 how I would use power query to combine the 26 and 27 table so that they could be in the same pivot table? Every column in both are identical. submitted by /u/dcal69 [link] [comments]
- How to merge tables from different files on one single excelhello! I'm no Excel connosieur, so I'm turning to this subreddit for some help! I have 12 files on my computer (one for each month of the year 2025) who each contain between 10 to 12 Excel documents. In total, I have approximately 132 Excel books that I need to merge into one single Excel. All of these Excel files have the same table format, except for maybe 5 that contain the same information as the rest. The rows, instead of being in the same order as the others, are mixed up. I've tried using Power Query, but half of the Excel files I import come up empty/only bring half of the information. Does anyone have any recommendations/YouTube video tutorials, or some tips/tricks to tackle this as quickly as possible? I'm sure anything is faster than just copying and pasting everything manually. Thank you for reading!! submitted by /u/mirapxoxo [link] [comments]
- combining two columns into one without vstack or an arrayi have two columns of names on two separate sheets of a workbook. on a third sheet, i want to combine all of the (unique, but that part is easy) names into one column. from there, some vlookups nested in iferrors pull in additional from whichever sheet the name came from (looks for the name in the larger spreadsheet; if it's there, do the vlookup, but if there's an error, check the smaller sheet and do the vlookup). vstack does this nicely, but because it returns an array, i can't use basic sorting anymore. the sort function exists, but we want to be able to do different sorts at different times and this sheet is for use by my less tech savvy coworkers, so i can't rely on that. a recommendation of =query1 & query2 was attempted but i ended up with the first row of each spreadsheet in one cell. please let me know if anyone else has ideas! i've been googling but keep hitting walls :') submitted by /u/nidoqueenofhearts [link] [comments]
Tagged with
#Excel compatibility#Excel alternatives for data analysis#financial modeling with spreadsheets#Excel alternatives#rows.com#merge#tables#values#Excel#power query#pivot table#efficiency#unique list#comparison#append#empty cells#stacking#copy paste#data manipulation#conversion