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