Jump to content

Excel Formula Help - Meta Thread


BTW

Recommended Posts

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

Link to comment
Share on other sites

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.

  • Hook 'Em 1
Link to comment
Share on other sites

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)

  • Hook 'Em 1
Link to comment
Share on other sites

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). 

  • Hook 'Em 1
Link to comment
Share on other sites

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.

  • Hook 'Em 2
Link to comment
Share on other sites

  • 1 year later...

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. 

Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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.  

  • Hook 'Em 1
Link to comment
Share on other sites

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.  

 

Link to comment
Share on other sites

1 hour ago, capnamerca said:

The former. 

 

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

image.png.0ba1f8f2407bf280f19cd92164bd2c14.png

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).  

  • Hook 'Em 1
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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. 

  • Hook 'Em 1
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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). 

image.png.794af92ade2f8a53e88cfa4cbad2e10a.png

 

image.png.364f1f2d98632b38093f8092ee5f34f0.png

 

Link to comment
Share on other sites

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).

 

image.png.fb8fa2dea13df75dbe04c5dc76b0bd69.png

 

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.

 

 

Link to comment
Share on other sites

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).

 

image.png.fb8fa2dea13df75dbe04c5dc76b0bd69.png

 

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.  

  • Hook 'Em 2
Link to comment
Share on other sites

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.

  • Hook 'Em 1
Link to comment
Share on other sites

  • 1 year later...

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

  • Hook 'Em 1
Link to comment
Share on other sites

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)

Link to comment
Share on other sites

16 minutes ago, Parliament said:

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

image.png.088ec0a022f93f179841bf40b2404635.png

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?

image.png.cb20d29ab6e618950c721f0acc49b7d1.png

Link to comment
Share on other sites

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?

image.png.cb20d29ab6e618950c721f0acc49b7d1.png

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 by Parliament
Link to comment
Share on other sites

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 by NoName
Link to comment
Share on other sites

  • 2 weeks later...
On 12/28/2023 at 12:50 PM, Parliament said:

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

image.png.088ec0a022f93f179841bf40b2404635.png

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. 

  • Hook 'Em 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



×
×
  • Create New...