- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
re: Excel is easy to learn
Posted on 4/23/19 at 2:03 pm to The Mick
Posted on 4/23/19 at 2:03 pm to The Mick
quote:
I forgot to tell you that sometimes the word/phrase in column A will have other shite in the cell with it. So it could be axx, axx 438, axx 2/15, etc. I'm trying to get it to pick up on any cell containing axx then grab the corresponding numerical value from B and summing it up in C.
A pivot will work and with that you can just search axx and it will give you all the results that contain those letters.
You could also use the left formula in a column next to your data. =left(Cell#,3) and drag it all the way down
> That will return the left three most characters in each cell. Thus create a new field that would just have axx.
Sumif will also work in place of a pivot after you do the left formula.
=SUMIF(Select the range for your text,"axx",select the range for the corresponding values)
This post was edited on 4/23/19 at 2:17 pm
2
Posted on 4/23/19 at 2:10 pm to leftovergumbo
Im getting an error 'destination reference is not valid'
Posted on 4/23/19 at 2:13 pm to tylercsbn9
quote:There's hundreds of different words/abbrevs in column A. I don't want to manually search each one. Is there a way that excel will find all cells in A with same abbrev and then grab the values from B and sum them? (and do that for each and every word/abbrev in A?
A pivot will work and with that you can just search axx and it will give you all the results that contain those letters.
This post was edited on 4/23/19 at 2:14 pm
Posted on 4/23/19 at 2:15 pm to The Mick
frick THIS, IM DONE
Posted on 4/23/19 at 2:18 pm to The Mick
Yeah, a pivot table
Posted on 4/23/19 at 2:19 pm to The Mick
quote:
There's hundreds of different words/abbrevs in column A. I don't want to manually search each one. Is there a way that excel will find all cells in A with same abbrev and then grab the values from B and sum them? (and do that for each and every word/abbrev in A?
You don't have to manually search for each one. That is the point of a pivot table. Set up a pivot. Got to search and type in axx and then make sure select all search results is selected.
> It will then return every line item that has axx in it for you and show a grand total.
This post was edited on 4/23/19 at 2:28 pm
Posted on 4/23/19 at 2:20 pm to The Mick
Sumif with a left formula, if the alpha characters are consistently the first 3 in that column
Posted on 4/23/19 at 2:20 pm to The Mick
quote:
frick THIS, IM DONE
I understand your pain. Excel use to drive me insane
Posted on 4/23/19 at 2:25 pm to tylercsbn9
I know I don't have to search each individual instance of axx, but I would need to do a search for each unique abbreviation in A of which there are hundreds. Id have to search axx, then search fm, then etc etc right?
Posted on 4/23/19 at 2:27 pm to The Mick
quote:
I know I don't have to search each individual instance of axx, but I would need to do a search for each unique abbreviation in A of which there are hundreds. Id have to search axx, then search fm, then etc etc right?
Yes. If you have a pivot set up and all you care about are results that have the text axx in them then simply type axx in the search part when you filter excel. It will return all results that contain the text axx no matter what comes after. I put a visual of that in my last post.
This post was edited on 4/23/19 at 2:28 pm
Posted on 4/23/19 at 2:27 pm to The Mick
Whatch a YouTube video on pivot tables.
10-12 minutes and you’ll be able to do what you need even through they can do a lot more.
10-12 minutes and you’ll be able to do what you need even through they can do a lot more.
Posted on 4/23/19 at 2:31 pm to Dirtysouthdeacon
quote:
Sumif
Works and will be your good enough solution at more of a basic Excel level.
Pivot table is the right, long-term tool.
Posted on 4/23/19 at 2:34 pm to tylercsbn9
I understand that process. My problem is I don't want to search axx, then pm, then sxu, then etc etc for hundreds of different abbrevs. Is there a way for excel to automatically group all matching abbrev instances in A then grab the corresponding numeric values from B?
Posted on 4/23/19 at 2:36 pm to The Mick
quote:
I understand that process. My problem is I don't want to search axx, then pm, then sxu, then etc etc for hundreds of different abbrevs. Is there a way for excel to automatically group all matching abbrev instances in A then grab the corresponding numeric values from B?
Yes by creating another column then using the left formula. Then pivot the data.
Posted on 4/23/19 at 2:36 pm to Ace Midnight
Pivot tables are only good if you don’t need to further manipulate the data
Posted on 4/23/19 at 2:36 pm to The Mick
just google any formula you need
Posted on 4/23/19 at 2:37 pm to The Mick
Algorithm or Block chain should do it.
But in all seriousness IFSUM or pivot table
But in all seriousness IFSUM or pivot table
This post was edited on 4/23/19 at 2:38 pm
Posted on 4/23/19 at 2:37 pm to tylercsbn9
quote:Pretty sure he said, it's not always three
You could also use the left formula in a column next to your data. =left(Cell#,3) and drag it all the way down
Posted on 4/23/19 at 2:38 pm to Ace Midnight
I think you should isolate the character strings you are looking for in a column then pivot on those character strings to sum the values in the other column.
Posted on 4/23/19 at 2:42 pm to castorinho
quote:
Pretty sure he said, it's not always three
Then he should use two characters instead of three and group the pivot table appropriately as needed.
Although I am guessing this will go right over his had since they don't even have a very basic understanding of excel.
Page 3 of 6
Popular
Back to top
Follow TigerDroppings for LSU Football News