•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Two tables/two workbooks. Matching data in columns to add data to a third.
Our take
If you're managing two separate workbooks with overlapping data, you can streamline your process by matching initials and dates to populate a third column. In Workbook 1, you have names linked to initials and dates, while Workbook 2 contains initials and dates without names. Instead of manually comparing and copying, consider using a formula or function that searches for matching initials and dates in Workbook 2 and automatically retrieves the corresponding names from Workbook 1.
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.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Two workbooks, two columns in second book could include matching data, neatest most efficient way to query between them on multiple fields using excel formula only, no VBA / Power Query etc?Not even sure how to surmise this but here we go: Workbook 1, column i has a reference code, "EntID" which could be a number OR text. Workbook 2, that reference code might be found in Column A, or Column B, or neither. In Workbook 1, I need 5 lookup columns to pull data from 5 corresponding columns in Workbook 2 where there is a match for " EntID" in EITHER column A or B. If there is no match then the cell should state "Not Found". I have it working by using nested if statements, isnumber & xmatch for the first lookup column to ascertain where the match is found and then xlookups on remaining columns depending on the result returned to that cell but the formula are huge if for no other reason than the file naming and locations (which cannot be changed) and it feels slow, clunky and unrefined. How would you do it? submitted by /u/Delinquent90 [link] [comments]
- Cross checking data from two different systems but they export in different formats - how to marry them up?I have a table with two sets of data exported from two different systems that I need to reconcile - names and values. Trouble is they export in different formats so I'm needing partial look-up? The lists are also not exact duplicates so there are names on one that don't appear on the other. Once the names have been matched up I then need to cross check the value to isolate any variances. So two formulas for two columns. thanks submitted by /u/pandoras_picnic [link] [comments]
- Is there a more efficient way to do what I am doing? (Merge tables keeping values)So I have three tables: https://preview.redd.it/mwzou7dva0qg1.png?width=964&format=png&auto=webp&s=744f10a3fad0171dfb408d62f01440e8cbdb87cc And I want to merge them into a table like this one: https://preview.redd.it/ztbg4ld3b0qg1.png?width=503&format=png&auto=webp&s=91dad64f697b523ac6c95cc1b868b9bd58653a3c 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? submitted by /u/Almogaver95 [link] [comments]
- Need a formula to match two columns and another 2 columns to return a valueFor the data I have to match up two names and two cities. If they match then they should return a value of the correct ID. The name, city, and ID that are being matched up to live in another sheet. So, if Column A (sheet 1)= Column A(sheet 2) and Column B(sheet 1) = Column D(sheet 2) then they'll return the correct ID from column E in sheet 2. Right now I have a formula two match the two names and return a value, but some of the names are repeats so I need a second match item to get the correct ID. This is my current formula to match up just the names and return the ID. =INDEX('Sheet 2'!B:B,MATCH(TRUE,EXACT(O6,'Sheet 2'!A:A),0)) submitted by /u/AlexisUCP [link] [comments]
Tagged with
#Excel alternatives for data analysis#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#rows.com#Excel compatibility#Excel alternatives#tables#workbooks#names#initials#dates#matching