Excel Formula Help - Meta Thread

[BTW 370]

My search of previous threads about excel brought a bunch of results about specific issues.  I also have a specific issue, but perhaps we need a thread for all things Excel so we don't have new ones for every issue?

If this is a bad idea, delete/ban etc ...

Spreadsheet A has thousands of lines of data.  Spreadsheet B has hundreds of lines of data.  If a line from spreadsheet B exists in Spreadsheet A, it needs to be deleted, hidden or removed from spreadsheet A.  I consider myself fairly savvy in Excel, but I've got no idea how to do this.

TIA

[Nice Guy Eddie 13540]

Is there an ID that you would use to match whether its a duplicate, or do you need to match on every data element?

[Lou 264]

Found this formula via Google:  Seems to work.

=IF(ISERROR(MATCH(A1,Records2!A:A,0)),"","Duplicate")

[BTW 370]

Outstanding! Many thanks.

[gyroprotagonist 2595]

1 hour ago, BTW said:

My search of previous threads about excel brought a bunch of results about specific issues.  I also have a specific issue, but perhaps we need a thread for all things Excel so we don't have new ones for every issue?

If this is a bad idea, delete/ban etc ...

Spreadsheet A has thousands of lines of data.  Spreadsheet B has hundreds of lines of data.  If a line from spreadsheet B exists in Spreadsheet A, it needs to be deleted, hidden or removed from spreadsheet A.  I consider myself fairly savvy in Excel, but I've got no idea how to do this.

TIA

i feel like there is a way to use an iferror(vlookup) formula to find lines and zero them out. 

=IF(IFERROR(VLOOKUP(A2,Small_Data_Set!$A$2:$A$10,1,FALSE),0)=0,A2,"")

so assume there are 2 tabs

- Large Data Set and Small Data Set

Large Data Set has values in Col A (starting in row 2) in its own tab.  Small Data Set has values from A2 to A10 in its own tab.

in the Large Data Set tab, enter this formula in Col C starting on same row as the data.  Copy all the way down.  Col C will now keep values not found and delete values found.

[Dahobbs 17811]

13 hours ago, BTW said:

My search of previous threads about excel brought a bunch of results about specific issues.  I also have a specific issue, but perhaps we need a thread for all things Excel so we don't have new ones for every issue?

If this is a bad idea, delete/ban etc ...

Spreadsheet A has thousands of lines of data.  Spreadsheet B has hundreds of lines of data.  If a line from spreadsheet B exists in Spreadsheet A, it needs to be deleted, hidden or removed from spreadsheet A.  I consider myself fairly savvy in Excel, but I've got no idea how to do this.

TIA

A few ways to do this depending on how many cells you need to match for each line.

First, if you just have 1 column you matching from each table, the simple match function posted earlier will work fine. You could also do it using the advanced filter function on spreadsheet a and setting the filter as the corresponding column in spreadsheet b (headers for the columns in a and b have to be the same). 

If you have multiple columns that need to be matched and want to use a function, you can uss sumproduct to do multiple criteria matching and then delete the offending lines from spreadsheet A. (=Sumproduct(--([@[criteria1]]=table2[criteria1])*([@[criteria2]...)

This link explains it more: https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/

Based on your description, I'd probably use one of those methods. But there are certainly others depending on how complex this is (e.g., get data/power query)

[Dahobbs 17811]

10 hours ago, gyroprotagonist said:

i feel like there is a way to use an iferror(vlookup) formula to find lines and zero them out. 

=IF(IFERROR(VLOOKUP(A2,Small_Data_Set!$A$2:$A$10,1,FALSE),0)=0,A2,"")

so assume there are 2 tabs

- Large Data Set and Small Data Set

Large Data Set has values in Col A (starting in row 2) in its own tab.  Small Data Set has values from A2 to A10 in its own tab.

in the Large Data Set tab, enter this formula in Col C starting on same row as the data.  Copy all the way down.  Col C will now keep values not found and delete values found.

Learn to use Index with Match or Index with Sumproduct. They are infinitely better choices to vlookup (or hlookup). 

[gyroprotagonist 2595]

9 hours ago, Dahobbs said:

Learn to use Index with Match or Index with Sumproduct. They are infinitely better choices to vlookup (or hlookup). 

 

i'll have to check it out.

 

[You don't know me 1161]

I know it's late and you have probably found a solution, but you can use the match function on multiple columns.

=MATCH(1,(A2=Sheet2!$A$2:$A$1001)*(B2=Sheet2!$B$2:$B$1001)*(C2=Sheet2!$C$2:$C$1001),0)

In this formula, I am returning the row number of the array on Sheet2 that has a match in all 3 columns.  After running this formula, I would sort all of the data on column D (the column in which I ran this formula) and then delete the rows that returned an error.

[capnamerca 572]

This seems to be a relatively recent Excel thread. I have a non-emergency question that is bothering the shit out of me. Also, I'm technically working in Google Sheets (it sucks), and for now that isn't changeable.

 

I have data in two columns - Player and Handicap. We're using this table to put together matchups for a tournament. Without getting into why I'm using the data the way I am, I need the following:

* In a separate section, I reference the main players/handicaps table to build matchups.

* When I reference a PLAYER (i.e. "=A1") in a cell ... I want the next cell over to reference that players handicap.

*Ideally, I could do something like "=A1+1" which would resolve to "=A2"

*The behavior is to allow me to change player references and have handicaps follow me around.

*The table of player/handicaps cannot be alphabetized (for reasons, this isn't negotiable), otherwise I could just VLOOKUP it.

 

I've already spent WAY more time on this than Ishould, and now it's mostly professional curiosity to see if I can get it to work. 

 

I've looked at INDIRECT and OFFSET, as well as playing around with TEXT() options, and nothing gets me what I want. 

[CooterBrown 18089]

This seems to be a relatively recent Excel thread. I have a non-emergency question that is bothering the shit out of me. Also, I'm technically working in Google Sheets (it sucks), and for now that isn't changeable.
 
I have data in two columns - Player and Handicap. We're using this table to put together matchups for a tournament. Without getting into why I'm using the data the way I am, I need the following:
* In a separate section, I reference the main players/handicaps table to build matchups.
* When I reference a PLAYER (i.e. "=A1") in a cell ... I want the next cell over to reference that players handicap.
*Ideally, I could do something like "=A1+1" which would resolve to "=A2"
*The behavior is to allow me to change player references and have handicaps follow me around.
*The table of player/handicaps cannot be alphabetized (for reasons, this isn't negotiable), otherwise I could just VLOOKUP it.
 
I've already spent WAY more time on this than Ishould, and now it's mostly professional curiosity to see if I can get it to work. 
 
I've looked at INDIRECT and OFFSET, as well as playing around with TEXT() options, and nothing gets me what I want. 

If I understand what you want correctly, couldn’t you use a lookup table to accomplish it?

[40acredropout 2908]

Are you saying that all you need to do is refer to Player X in a cell and have his handicap pop up in the next?  Easiest way to do that would be index / match combination.

If you’re saying you want to refer to Player X and have a formula to automatically determine Player Y by some criteria (closest handicap?), that would take an extra step.  

[capnamerca 572]

The former. 

6 hours ago, 40acredropout said:

Are you saying that all you need to do is refer to Player X in a cell and have his handicap pop up in the next?  Easiest way to do that would be index / match combination.

If you’re saying you want to refer to Player X and have a formula to automatically determine Player Y by some criteria (closest handicap?), that would take an extra step.  

 

[40acredropout 2908]

1 hour ago, capnamerca said:

The former. 

 

Something like this should accomplish what you're looking for then:

I would also add a drop down list (Data->Data Validation->Allow: "List" and set your "Player" column as the source) to select the players in your matchup section (I guess that would be column D in my example).  

[capnamerca 572]

If the data were cleanly in one table, that works. But for various reasons, it's not, thus my desire to be able to operate on a reference. (believe me, I get why the data layout in my sheet is sub-optimal, but here we are ).

[Cheeseweasel 26438]

2 hours ago, capnamerca said:

If the data were cleanly in one table, that works. But for various reasons, it's not, thus my desire to be able to operate on a reference. (believe me, I get why the data layout in my sheet is sub-optimal, but here we are ).

Sounds like vlookup is your answer. 

[Dahobbs 17811]

2 hours ago, capnamerca said:

If the data were cleanly in one table, that works. But for various reasons, it's not, thus my desire to be able to operate on a reference. (believe me, I get why the data layout in my sheet is sub-optimal, but here we are ).

Can you just post a link to the worksheet? I don't understand what you're saying. This sounds like something I could fix I'm 5 minutes if I could see it. 

[Dahobbs 17811]

2 hours ago, capnamerca said:

If the data were cleanly in one table, that works. But for various reasons, it's not, thus my desire to be able to operate on a reference. (believe me, I get why the data layout in my sheet is sub-optimal, but here we are ).

Take a look again at what 40acre posted. He is using a reference. He has the reference table of handicaps, and then separately the formula that pulls from that table (which is what you asked for). If you don't have the handicaps listed somewhere, I don't understand what type of reference you're asking for. You have to refer to something else, somewhere.  If you have multiple tables of players, that is ok too. It just requires a modification to the formula, which will depend on how many player tables you have. As I said above, probably easiest to fix if you can just post the worksheet. 

[capnamerca 572]

all I want is "when I reference a cell, I'd like the cell next door to grab the cell next to the cell I just referenced."

 

But, I've rearranged my source data to more easily support INDEX MATCH. Appreciate the insight all.

[blacklab 11994]

if you don't need the handicap in a separate column and are just looking at it why not instead of

=A1 

do a

=CONCAT(A1,concat(" (",concat(A2,")")))

which will result in a "FJ (5)"

concat on google docs only allows 2 items thus the messy syntax

 

[Dahobbs 17811]

6 minutes ago, capnamerca said:

all I want is "when I reference a cell, I'd like the cell next door to grab the cell next to the cell I just referenced."

 

But, I've rearranged my source data to more easily support INDEX MATCH. Appreciate the insight all.

I'm still not really understanding how this fits into your broader request. However, spreadsheets (at least excel) supports relative references by default. For instance, if you put in cell A3 "=B2", you can autofill that through the rest of column A such that A4 = B3 , A5 = B4 and so on.  If you're dealing with a range and a not a defined table, you literally just need to copy cell A3 and highlight the rest of column A and then paste (or in excel you can drag the bottom right corner of cell A3 down and do the same thing). 

 

 

[capnamerca 572]

Apologies for going down a rabbit hole here :).

 

I"m building out a list of matchups for a golf tournament. The player pool is divided - three teams of twelve. We're currently moving teams around, playing with matchups, etc. As we move players around, I want their handicaps to follow. I"m not skilled enough to figure out index+match when the data is in three separate tables. But what I do know is that handicap is always one call to the right of the player name, and so what I really want is an indirect reference that references another reference (lol).

 

 

In the table above ... two separate tables of players. Building the matchups on the right. G5 contains "=b4", and so gets populated with 'Bill'. What I was gunning for was something I could put into H5 that would say "ah, I see you referenced cell b4, I'll get the value from one to the right of b4" and then it would resolve itself to a reference of "=b5", and thus populate itself with '5'. 

 

I think from what you said above that Index can handle searching across multiple tables. I think that's the official answer. What I've done is moved to one large player table, and am now using index+match, and all is well.

 

It's also hard to explain things in writing, and we've now officially spent more time messing with this than it's taken me to just rearrange the data into one big table :). I really do appreciate ya'lls suggestions.

 

 

[Dahobbs 17811]

21 minutes ago, capnamerca said:

Apologies for going down a rabbit hole here :).

 

I"m building out a list of matchups for a golf tournament. The player pool is divided - three teams of twelve. We're currently moving teams around, playing with matchups, etc. As we move players around, I want their handicaps to follow. I"m not skilled enough to figure out index+match when the data is in three separate tables. But what I do know is that handicap is always one call to the right of the player name, and so what I really want is an indirect reference that references another reference (lol).

 

 

In the table above ... two separate tables of players. Building the matchups on the right. G5 contains "=b4", and so gets populated with 'Bill'. What I was gunning for was something I could put into H5 that would say "ah, I see you referenced cell b4, I'll get the value from one to the right of b4" and then it would resolve itself to a reference of "=b5", and thus populate itself with '5'. 

 

I don't believe this is possible in Excel or Google Docs, at least absent some custom VBA code. What you've done to move everything over is the correct answer.  

[capnamerca 572]

4 minutes ago, Dahobbs said:

I don't believe this is possible in Excel or Google Docs, at least absent some custom VBA code. What you've done to move everything over is the correct answer.  

My old days as a firmware programmer in college pop up in weird ways sometimes :). All I want to build is a translation lookaside table (reference to a reference). But yes, moving 24 rows around and joining them with 12 rows in the top table is a drastically simpler way to do things lol. Thanks again.

[Cheeseweasel 26438]

Have you tried turning it off and turning it back on again?

[capnamerca 572]

1 minute ago, Cheeseweasel said:

Have you tried turning it off and turning it back on again?

Many, many times. 

[Parliament 14373]

Long time listener, first time caller

I'm working with a large data set, combining it with another data set.  The one set doesn't have dedicated columns for each product, but a separate line instead.  (Zovirax and Valtrex in this case.)

I need to have ALL items for a customer on the same row; to make a column for South Austin's Mom's Valtrex, Zovirax, etc.  I think this can be done with a pivot table, but the end users of this thing won't understand that.  They want filterable columns.  And TBH, I'm the same.

So how do I convert this thing?  Is there a vlookup function that can search for TWO criteria?  Google searches say I need to build a "helper column" but I hope to avoid that complication.

TIA

[NoName 16618]

5 minutes ago, Parliament said:

Long time listener, first time caller

I'm working with a large data set, combining it with another data set.  The one set doesn't have dedicated columns for each product, but a separate line instead.  (Zovirax and Valtrex in this case.)

I need to have ALL items for a customer on the same row; to make a column for South Austin's Mom's Valtrex, Zovirax, etc.  I think this can be done with a pivot table, but the end users of this thing won't understand that.  They want filterable columns.  And TBH, I'm the same.

So how do I convert this thing?  Is there a vlookup function that can search for TWO criteria?  Google searches say I need to build a "helper column" but I hope to avoid that complication.

TIA

yo, mind sending a screenshot of what you are talking about or upload a de-identified example? i do a lot of work in this specific kind of space but want to make sure i fully understand what you need (feel free to take it to PMs)

[Parliament 14373]

Crap sorry forgot the screenshot.  I need to fill the highlit cells:

[NoName 16618]

16 minutes ago, Parliament said:

Crap sorry forgot the screenshot.  I need to fill the highlit cells:

you just need the yellow columns to be total quantity, right?

does Huge Jass ONLY show up 2x column wise (once for Valtrex and once for Zovirax) or is it possible he has multiple Valtrex columns in the dataset?

if it's just once it's way easier to do with a vlookup - you want it to be something like this, with the total quantity filterable, right?

[Gene Parmesan 4023]

A pivot table would do all that for you.  I don't understand what you mean the end user won't understand?  Just copy/paste as values if they don't want the pivot table functions.  

[Parliament 14373]

8 minutes ago, NoName said:

you just need the yellow columns to be total quantity, right?

does Huge Jass ONLY show up 2x column wise (once for Valtrex and once for Zovirax) or is it possible he has multiple Valtrex columns in the dataset?

if it's just once it's way easier to do with a vlookup - you want it to be something like this, with the total quantity filterable, right?

That's what I want.  So I need to Concacatate (sp) the person and the product into two helper columns, then vlookup from there?  No way to avoid the helper columns?

Edited December 28, 2023 by Parliament

[NoName 16618]

16 minutes ago, Parliament said:

That's what I want.  So I need to Concacatate (sp) the person and the product into two helper columns, then vlookup from there?

not sure how proficient you are in excel but this SHOULD work even if one entry has multiple rows with data in it. it lets you have your data on one page and then your rollup (that you can filter) on another tab

i've done it with vlookups before, BUT if you ever have any chance that there are multiple rows with data in it then vlookup isn't your answer. so i re-did it with sumifs to solve that potential future problem. not sure if the data is exported or manual but if it's manual this should maybe make it easier?

i'm a big fan of automatically generated helper columns, which is how i have set it up. i use dashes instead of =concat just because it's how my mind works but you can also do =concat(A1,"-",B2) if you prefer.

rollup tab should automatically update, can be filtered and works if there is 1 line or 4 lines for Hugh for Valtrex for example.

orange columns = helper and you can hide the ones on the first tab to make it easier for end users BUT if they get deleted that will break the sumif

works great, a pivot would be better but that blows some end users minds so i understand why not everyone likes that.

parliment example.xlsx

also i'm sure some wizard will come along with a better/easier way of doing it, this is just how my brain works on it. again, i do a lot of this kind ot stuff that would be easier with an actual database tool but we have excel and that's what we are going to use haha

Edited December 28, 2023 by NoName

[DallasHorn26 586]

You can also use a sumif formula, it sums the total in column c if column a matches column A11 and Column B matches B10, you would do the same in column C. 

[Dahobbs 17811]

On 12/28/2023 at 12:50 PM, Parliament said:

Crap sorry forgot the screenshot.  I need to fill the highlit cells:

I'm probably late on this, but here goes:

Pivot table is the easiest way. Just copy and paste the data into a new range if you're worried about pivot functions confusing your customer. I'm not sure what exactly you'd be filtering on in the new columns other than the person's name. It would be an odd use case to want to filter on particular quantities of the medication, so maybe there is something to your project that I'm missing. 

Assuming you hate pivot tables, you could also use the sumproduct formula in your second table. Assuming you turn your ranges into tables (Control 'T') and name the top table "Original" and the bottom table "Converted", you'd enter the following formula in B11 through 13:

=SUMPRODUCT(--(Original[Name]=[@Name])*(Original[Product]=Converted[[#Headers],[Valtrex]])*(Original[Quantity])*1)

and this formula in C11 through 12

=SUMPRODUCT(--(Original[Name]=[@Name])*(Original[Product]=Converted[[#Headers],[Zovirax]])*(Original[Quantity])*1)

Sumifs function would also work. 

The advantage to sumproduct and sumifs is that they don't require the helper column that @NoName added. All that said, Pivot Tables are infinitely superior for what you're trying to do here, especially if you have more than a couple of medications/products at issue. With pivot tables, you wouldn't have to write new formulas for each column. 

Finally, let's say you're actually trying to merge two different sets rather than simply transform an existing data set (i.e., both data sets have unique information that has to be included in the merged data set). In that case, you'll want to learn to use PowerQuery/ the "Get Data" feature in excel. It is extremely powerful for everything from merging data sets to transforming existing data in much more complex ways. I'm not going to explain it all here, just saying it may be something you want to learn about and/or take a class on. 

[Parliament 14373]

Next one, a weird problem I've seen once before.  I have a medium-sized data set from some random, likely old, database. Customer name, mailing address, purchase history and such.  I need to vlookup some more data in from a second spreadsheet.  As per usual I copy the data from that second spreadsheet onto a new, empty sheet in the OG, do a conventional vlookup with the customer name and BAM.

No bam this time.  I see the customer's row in each sheet but it doesn't pull the info over; just a #N/A or whatever.  Here's the weird part.  I add a new row to the spreadsheet, manually type the customer's name in the cel, EXACTLY the same, and the vlookup.  It pulls the data for the manually-typed cel, but not for the cel that came with the data set.  It's like "Mike Johnson" is in a format you and I can see, but Excel recognizes it as something else.

I copied the original data set and pasted "values only" in a fresh sheet.  Formatted the column as text.  All no joy.

Am I fooked?

[0xdeadbeef 1653]

23 minutes ago, Parliament said:

Next one, a weird problem I've seen once before.  I have a medium-sized data set from some random, likely old, database. Customer name, mailing address, purchase history and such.  I need to vlookup some more data in from a second spreadsheet.  As per usual I copy the data from that second spreadsheet onto a new, empty sheet in the OG, do a conventional vlookup with the customer name and BAM.

No bam this time.  I see the customer's row in each sheet but it doesn't pull the info over; just a #N/A or whatever.  Here's the weird part.  I add a new row to the spreadsheet, manually type the customer's name in the cel, EXACTLY the same, and the vlookup.  It pulls the data for the manually-typed cel, but not for the cel that came with the data set.  It's like "Mike Johnson" is in a format you and I can see, but Excel recognizes it as something else.

I copied the original data set and pasted "values only" in a fresh sheet.  Formatted the column as text.  All no joy.

Am I fooked?

Probably some non-printable characters.   Insert a new column B and do =CLEAN(A1)

 

Edited June 21 by 0xdeadbeef

[Parliament 14373]

That's a great one.  Never heard of a problem like that and I appreciate the tip.

Didn't work though.  What else you got?

[Dahobbs 17811]

14 minutes ago, Parliament said:

That's a great one.  Never heard of a problem like that and I appreciate the tip.

Didn't work though.  What else you got?

Can you attach a sample from each sheet so we can see what it looks like? I'm sure there is a solution here, but it is hard to diagnose without seeing it. One option might be using the search function within sumproduct instead of a lookup. 

Edited June 21 by Dahobbs

[Parliament 14373]

I shrunk it to 3 entries.  Aaron Pape and Blake Satterlie are real people.  Whatever glitch came over, the CLEAN function doesn't fix.  I need to manually type the name into the column the vlookup is accessing.

If we figure this out I'll bring strippers to the next Surly tailgate.

 

Surly.xlsxSurly.xlsxSurly.xlsx

[Dahobbs 17811]

The issue is that your old sheet has spaces after the end of the name, so it doesn't exactly match your lookup sheet. Names really aren't an ideal field for a lookup because you can easily have people with the same name. We have a few options to address it as is, but I want to make sure there isn't some other field to match on that may work better. 

Edited June 21 by Dahobbs

[Dahobbs 17811]

@Parliament

Here is something that'll work so long as you don't have different people with the same name in either the original data or your new data. If you have do have individuals with the same name, it'll get more complicated.

 

Surly - Copy.xlsx

Edited June 21 by Dahobbs

[Parliament 14373]

Yeah I live the life of "different people, same names."  Our company has two entirely different systems for pretty much the same customers.  The only functional "key" to link a list is their email.  If i can get the vlookup to grab the easy wins, I can do the rest manual.

"xlookup" is what I want? 

 

Edited June 22 by Parliament

[Dahobbs 17811]

2 hours ago, Parliament said:

Yeah I live the life of "different people, same names."  Our company has two entirely different systems for pretty much the same customers.  The only functional "key" to link a list is their email.  If i can get the vlookup to grab the easy wins, I can do the rest manual.

"xlookup" is what I want? 

 

So, the way I setup the spreadsheet, I first added a new column in your new data with the emails. The new column searches for the customer + anything. The point is that your old data has random characters (mainly spaces) after the customer name. Since I don't know for sure what those characters are going to be, we need a way to get the old names over to your current list. We can then use those to cross reference on the old list to bring the email over. Xlookup let's you add wildcards to your search criteria, but not the search array. So we have to do it the way I setup. You need that new column in the second tab to make it work. You should be able to just copy what I did to get it to apply to the rest of your data. If you need a more detailed explanation, feel free to PM me and I can walk you through it over a zoom or something. 

[NoName 16618]

On 6/21/2024 at 4:41 PM, Dahobbs said:

The issue is that your old sheet has spaces after the end of the name, so it doesn't exactly match your lookup sheet. Names really aren't an ideal field for a lookup because you can easily have people with the same name. We have a few options to address it as is, but I want to make sure there isn't some other field to match on that may work better. 

if folks are coming into this in the future and have a specific issue like this, your friend is:

=TRIM(cell to clean here)

so "=TRIM(A1)"

i'm sure @Dahobbs knows this - not calling him out specifically, just dropping it in here for others in the future

[Dahobbs 17811]

54 minutes ago, NoName said:

if folks are coming into this in the future and have a specific issue like this, your friend is:

=TRIM(cell to clean here)

so "=TRIM(A1)"

i'm sure @Dahobbs knows this - not calling him out specifically, just dropping it in here for others in the future

Yeah, trim is great if extra spaces are the only problem. I didn't use it here because I only had a limited sample and couldn't be sure there weren't other character issues. But you're right, it may well solve @Parliament's problem. 

[NoName 16618]

9 minutes ago, Dahobbs said:

Yeah, trim is great if extra spaces are the only problem. I didn't use it here because I only had a limited sample and couldn't be sure there weren't other character issues. But you're right, it may well solve @Parliament's problem. 

the space stuff is an issue in my world around names specifically. yours is a better option for probably everything else haha