Question description:
This user has given permission to use the problem statement for this
blog.
I'm trying to make a formula that can take numbers 1-3 from a column of 8 cells which are generated by formulas and apply them to another column of 8 cells. There is the need to be able to use the same number multiple times and i would like any possible blank cells to be used last of the 8.
Solved by A. Y. in 35 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
04/04/2018 - 01:43
I currently use excel every day and will most likely for the near future
Excelchat Expert
04/04/2018 - 01:43
Hello, welcome to excelchat
User
04/04/2018 - 01:43
hi
Excelchat Expert
04/04/2018 - 01:43
How can I help you?
User
04/04/2018 - 01:43
I'm trying to write a formula
Excelchat Expert
04/04/2018 - 01:44
OK
Excelchat Expert
04/04/2018 - 01:44
Can you explain with an example?
User
04/04/2018 - 01:44
I'm trying to make a formula that can take numbers 1-3 from a column of 8 cells which are generated by formulas and apply them to another column of 8 cells. There is the need to be able to use the same number multiple times and i would like any possible blank cells to be used last of the 8.
User
04/04/2018 - 01:46
the numbers 1-3 are generated by referencing words from another column
Excelchat Expert
04/04/2018 - 01:46
Lets say, we have 8 cells here
Excelchat Expert
04/04/2018 - 01:46
highlighted in yellow
Excelchat Expert
04/04/2018 - 01:47
where will these values come from?
User
04/04/2018 - 01:47
The first column has 3 different word options
Excelchat Expert
04/04/2018 - 01:48
What are the options?
User
04/04/2018 - 01:48
this sounds weird but it is for a missile engagement tool for the navy
User
04/04/2018 - 01:48
the options are kill, miss, and no shot
Excelchat Expert
04/04/2018 - 01:48
Haha
User
04/04/2018 - 01:48
and then blank is the last option
Excelchat Expert
04/04/2018 - 01:49
So, you want a drop down list in the 8 cells?
User
04/04/2018 - 01:49
no,the next column takes whichever word appears and turns kill into a 1,
Excelchat Expert
04/04/2018 - 01:50
Kill=1, miss=2, noshot=3?
User
04/04/2018 - 01:50
those three words are generated by about 12 other formulas
User
04/04/2018 - 01:50
yup
User
04/04/2018 - 01:50
there is the possibility that each of those may appear multiple times
User
04/04/2018 - 01:51
the are two teams the first is the top 8 rows and the other is rows 10-17
Excelchat Expert
04/04/2018 - 01:52
Hows this?
Excelchat Expert
04/04/2018 - 01:52
Does this solve your purpose?
User
04/04/2018 - 01:52
I'm trying to come up with the formula to take the numerical values from the top 8 and move them to the bottom 8
User
04/04/2018 - 01:52
that is pretty much what i have at this point
Excelchat Expert
04/04/2018 - 01:53
Top 8 values are the worst, so wanna keep them in the bottom?
User
04/04/2018 - 01:54
No, it is supposed to take whether each missile hit on the top and then move it to the other team and effectively kill an aircraft
User
04/04/2018 - 01:54
ultimately it will also work the other way, bottom missiles will kill the top team
Excelchat Expert
04/04/2018 - 01:55
Now the 8 values in B column
User
04/04/2018 - 01:55
and then I will be able to say how many of each team survived the engagement
Excelchat Expert
04/04/2018 - 01:55
ds to be done with them?
Excelchat Expert
04/04/2018 - 01:55
what needs*
User
04/04/2018 - 01:56
they need to be moved to the bottom eight rows of the C column
Excelchat Expert
04/04/2018 - 01:56
Column C has no end
User
04/04/2018 - 01:57
sorry, rows 10-17 of the C column
Excelchat Expert
04/04/2018 - 01:58
Next?
User
04/04/2018 - 01:58
So the problem is that it needs to be a formula that accounts for the possibility that some of the shooting aircraft in rown 1-8 may not be shooting at that time
Excelchat Expert
04/04/2018 - 01:59
You need a blank option too?
User
04/04/2018 - 01:59
therefore if row 3 is already killed and blank it would need to account for that and move everything else up
User
04/04/2018 - 01:59
yes
User
04/04/2018 - 01:59
just leaving the a column empty will suffice at this time
Excelchat Expert
04/04/2018 - 02:01
Yeah?
User
04/04/2018 - 02:01
say in a previous move that aircraft 1 and 3 got killed then both of those would be blank but 2 and 4-8 would still shoot.
Excelchat Expert
04/04/2018 - 02:02
Now, Row 3 is blank, you want row 13 to move up to row 12?
User
04/04/2018 - 02:02
it needs to auto fill to the live aircraft
User
04/04/2018 - 02:02
ya
Excelchat Expert
04/04/2018 - 02:02
Exactly that?
User
04/04/2018 - 02:03
ya, it needs to move all of the numerical values to the top of the list and bump the blanks to the bottom
User
04/04/2018 - 02:04
if able i would like the formula to be able to use duplicates because there may be multiple of each number and there may not be any of some numbers.
User
04/04/2018 - 02:06
the problem with this formula is that it is using row 4s kill twice now
Excelchat Expert
04/04/2018 - 02:09
Moving is necessary?
User
04/04/2018 - 02:10
yes, there is the potential that there will be a mixture of people alive on both the top and bottom so the cells need to fill in blank spaces
User
04/04/2018 - 02:10
using the current formula if only row 8 is alive and got a kill then it would kill all of the other team
User
04/04/2018 - 02:12
my last attempt was a lookup and countif formula which failed because it won't allow duplicates
Excelchat Expert
04/04/2018 - 02:13
I can't really figure out something
Excelchat Expert
04/04/2018 - 02:14
Not really able to understand what to do :/
User
04/04/2018 - 02:14
OK, thanks anyways
Excelchat Expert
04/04/2018 - 02:14
Let me check ne last thing
Excelchat Expert
04/04/2018 - 02:14
one
User
04/04/2018 - 02:14
ok
Excelchat Expert
04/04/2018 - 02:15
You can create a macro for that
User
04/04/2018 - 02:16
if it helps that is what my document looks like
User
04/04/2018 - 02:16
the g column would try to switch the top 8 to the bottom 8 and vice versa
Excelchat Expert
04/04/2018 - 02:16
Sorry, I don't think i am able to get anything now
Excelchat Expert
04/04/2018 - 02:16
:/
Excelchat Expert
04/04/2018 - 02:17
Bad day :(
User
04/04/2018 - 02:17
granted, it didn't populate the formulas
User
04/04/2018 - 02:17
ok
Excelchat Expert
04/04/2018 - 02:17
Hope you find something, take care
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
