How to find if two inputs in different columns occur in the same row
Our take
So I have a set of data with ID numbers that recur. Let's say for example it's people who were called in a call center and some people are called repeatedly, and I have a record for each call. I need to pull a list of all IDs and see which people have had which results.
So for example:
ID Result
123 Voicemail
456 Call back
123 Hang up
789 Hang up
I need to know if each of these people has hung up on us, and if each of them has received a voicemail. Vlookup will only tell me the first result, so I can't rely on sorting the data, since IDs will have multiple results.
I've tried creating a new column with the ID and result in the same cell, but my problem is that I can't figure out how to get Excel to tell me if the cell contains the ID and the result I'm looking for. Result is easy, but there are several hundred IDs, so I can't type them out in the formula. (And of course, the IDs aren't all a consistent length, because the data gods hate me.)
I've also tried an ifand (if(and(ColumnA,A2,ColumnB,B2 etc etc), but that just tells me if those IDs and results occur anywhere in columns A or B, which is not what I need. I need to know if they're in the same row.
I also need to document this process for my boss, who is not a regular Excel user, and ideally set up a sheet so she can just paste the data in and get the result she needs. So ideally I'd love to find a solution that doesn't involve VBA, power suite, macros, etc.
Any thoughts?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Need to return a value based on two different search criteria, multiple lines, VLOOKUPHi All, I have a report that I run at work that doesn't come out in a way that makes it easy to work with in an excel spread sheet. The best i can get it is a txt file that when pasted spreads values into different cells. I use VLOOKUP in other sheets to find one value and return another value in the same row. I don't know how to (or if it's even possible) to do this with multiple values on different rows. Could really use some help. I need some sort of VLOOKUP/XLOOKUP type of formula that does the following: -Searches Column A for a specific value -Then Searches Column B (Below where Column A value was found) for another value -Returns the value in Column C (Same Row as B) based on the value from B I'm pretty much stuck using Excel for this as my company doesn't allow outside/other programs to further organize the data. Macros may also be an option if anyone knows any. Thanks in advance with any answers. submitted by /u/QuandoOmniFluncas [link] [comments]
- 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]
- How to autofill data from a row to a column on a different sheet in the same folder?I've been struggling with some solutions I've found on the forum but after 1.5hrs I'm close to giving up and manually entering data - which is bound to cost me another 28 hrs. Hoping someone has the solution I'm looking for and is willing to share.. I've exported questionnaire results from Mentimeter to Excel. The document output is formatted automatically in a way that uses columns for unique respondents, followed by their answers in the same column but along individual cells on that column's row, meaning the first entry is A2 and the last entry is in cell CQ2 or something. I would like to make this more user-friendly by: 1) putting each respondent's answers in their own sheet in the folder, and 2) by listing the questions in the first column and the answers in the next columns pretty much 'the other way around'. Currently it looks like this; the answers I need are listed in !VotersF3 to !VotersCQ3. The next respondent's answers are in !VotersF4 through to !VotersCQ4 and so on. What I'm looking for would ideally display answers in !AnswerA3 through to A80. When I manually select !AnswerA3 and click on !VotersF3, logically it does what I want. When I then drag down to autofill, equally logically the sheet enters !VotersF4 instead of !VotersB3 as it's a row vs column problem. I've tried different version of INDEX and TRANSPOSE but I can't get a working formula from that. Would anyone be able to provide me with the correct solution for doing this? I've got another 20+ respondents answers that need to be 'easy to view' instead of scrolling 500 screens horizontally.... Thank you Excel wizards! :) submitted by /u/Lost_Mud2097 [link] [comments]
- Getting the sum of multiple row if an identifier is matched?So I have several sheets of data. Sheet1: Identifier https://preview.redd.it/7p7vv2lg4gyg1.png?width=128&format=png&auto=webp&s=7bce4e6ca02e9cb9ea5e3080ade52017af0a179d Sheet2: Find the identifier within the row. I have hundreds of rows of data. If an identifier is in the first 3 values, I want to add the first column after the break, 10. If the identifier is in the second set of 3 values, I want to add the column after that, 100. I want to add a column Sheet1 to contain the sum of that given identifier for my all my data rows. The data in the 6 columns will never have duplicates. https://preview.redd.it/vjdopflt4gyg1.png?width=1336&format=png&auto=webp&s=abf8ad27472f93a56dcd8864f42dcbea7cb6aa7a It would be something like this if I wanted to add a single row, but I want it to add all of my data rows. =IF(COUNTIF(Sheet1!B1:D1, A2) > 0, Sheet1!L1, 0) + IF(COUNTIF(Sheet1!E1:G1, A2) > 0, Sheet1!M1, 0) How do I find a single sum for a given identifier for multiple rows of data where I want to get the sum of a column if that identifier is in a different column? submitted by /u/Confused_Crossroad [link] [comments]