# Excel - COLUMN Function Problem - Expert Solution

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

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.