- 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
one more question for the excel ninjas
Posted on 2/28/23 at 12:05 pm
Posted on 2/28/23 at 12:05 pm
I figured out how to take a string of options, separated by commas in one cell, and break them apart to put them each in a their own column.
so from:
dog, cat, bird, snake (in one cell)
to
dog | cat | bird | snake |
but, every person did not have all of the above so each of the columns do not contain the same thing.
dog | cat | snake |
cat | snake |
(no dog so cat is pushed to the beginning and in the dog column)
How do I line them all up in columns of the same item so that I can total them at the bottoms to know how many I have of each?
so from:
dog, cat, bird, snake (in one cell)
to
dog | cat | bird | snake |
but, every person did not have all of the above so each of the columns do not contain the same thing.
dog | cat | snake |
cat | snake |
(no dog so cat is pushed to the beginning and in the dog column)
How do I line them all up in columns of the same item so that I can total them at the bottoms to know how many I have of each?
2
Posted on 2/28/23 at 12:28 pm to Sir Drinksalot
Try the below.
=MID(Y7,SEARCH(AA6,Y7,1),LEN(AA6))
y7 = source string (dog,cat, bird, snake)
aa6 = column name (i.e. Cat)
Hopefully I don't get shown up on this one.
=MID(Y7,SEARCH(AA6,Y7,1),LEN(AA6))
y7 = source string (dog,cat, bird, snake)
aa6 = column name (i.e. Cat)
Hopefully I don't get shown up on this one.
Posted on 2/28/23 at 12:43 pm to dtett
You win on this one. Wouldn't have thought to handle it like that. Had to try it out real quick. But I'll add a little bit of help. If the animal is missing in the string, the value is returned with #VALUE result.
You can wrap your formula in a =IFERROR(yourformula,"") and it will return an empty string (or whatever you put in the "").
You can wrap your formula in a =IFERROR(yourformula,"") and it will return an empty string (or whatever you put in the "").
Posted on 3/3/23 at 12:25 pm to WhiskeyThrottle
Use the text to columns function to split by comma:
LINK
That will give you everything as an individual cell. Say that range of values go from Cell A1 to Z26.
In a seperate tab, use the "tocol()" function to stack everything neatly into a single column. =tocol(A1:Z26)
Once in a single column, you can then use a countif() formula to count the number of times each animal appears. So say the tocol() output goes from A1:A676, in cell c1 you could write =countif(A1:A676,"Dog") - and that would tell you how many dogs there are.
LINK
That will give you everything as an individual cell. Say that range of values go from Cell A1 to Z26.
In a seperate tab, use the "tocol()" function to stack everything neatly into a single column. =tocol(A1:Z26)
Once in a single column, you can then use a countif() formula to count the number of times each animal appears. So say the tocol() output goes from A1:A676, in cell c1 you could write =countif(A1:A676,"Dog") - and that would tell you how many dogs there are.
Posted on 3/3/23 at 7:47 pm to Sir Drinksalot
You don't need to line them all up in a column. Use the COUNTIF function.
For example:
=COUNTIF(A2:Z2,"cat")
This formula will count the number of cells in row 2 from column A to column Z that say cat.
You can repeat that formula with each of the different animals in the quotation marks. If you cut and paste the cell, it may change your array, so you should verify your array (a2:Z2 in my example).
For example:
=COUNTIF(A2:Z2,"cat")
This formula will count the number of cells in row 2 from column A to column Z that say cat.
You can repeat that formula with each of the different animals in the quotation marks. If you cut and paste the cell, it may change your array, so you should verify your array (a2:Z2 in my example).
This post was edited on 3/3/23 at 7:48 pm
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News