Going back to the top of a list

[Gmax]

Hello everyone,

First of all, I don’t have the files available so I’ll try to do my best to be clear !

Let’s imagine we have two columns, in the first one we have the employee’s number (it might be a number of numbers with letters). In the second column we just have a number (we can suppose that this number is 1,0,etc and that it can represents whatever we want, if he smokes, etc, we don’t really care here). Some employees (so illustrated by their employee’s number) don’t have a number in the second column (we can imagine that we don’t have the information about them).

Now the problem ! What I am trying to do is to find a formula that could list the number of employee from the top to the bottom but in a special way: I want to see if there is something in the second column and as long as something is present we just continue listing the employees (their number) but when a cell in the second column is empty, I would like my formula to start back from the top (so from my first employee’s number) while continuing after the blank in the second column. This might seem confusing since I have used the example of smoking for column two but the data are a bit more complex and it was the first example I have found !


I hope my problem is clear and I already thank you for your help !

 

[Jamie McMillan]

Hello everyone,

First of all, I don’t have the files available so I’ll try to do my best to be clear !

Let’s imagine we have two columns, in the first one we have the employee’s number (it might be a number of numbers with letters). In the second column we just have a number (we can suppose that this number is 1,0,etc and that it can represents whatever we want, if he smokes, etc, we don’t really care here). Some employees (so illustrated by their employee’s number) don’t have a number in the second column (we can imagine that we don’t have the information about them).

Now the problem ! What I am trying to do is to find a formula that could list the number of employee from the top to the bottom but in a special way: I want to see if there is something in the second column and as long as something is present we just continue listing the employees (their number) but when a cell in the second column is empty, I would like my formula to start back from the top (so from my first employee’s number) while continuing after the blank in the second column. This might seem confusing since I have used the example of smoking for column two but the data are a bit more complex and it was the first example I have found !


I hope my problem is clear and I already thank you for your help !

Hello GMAX,

It is a little confusing.

I think that you want a database query; without using a database. So that you have one list (In column C with the employee's number - without any spaces in the list.

A little bit of work for you; but I will talk you through it.

In another column that will never be used. I am starting in column M - on the first row M1 type in 1. 2nd row (M2) =M1+1 and copy the M2 cell down to the end of the employee list. [This is just 1 - Number of employees]

In Cell N1 type the formula =IF(B1="","",A1) copy this down to the end.

In Cell O1 type the formula =IF(B1="","",M1) copy this down to the end.

Almost done. - You should now have something like the image below:-

Now the best part.

In cell C1 type the formula =IFERROR(VLOOKUP(SMALL($O$1:$O$18,M1),$M$1:$N$18,2),"") {Copy it from here and just paste it}

Copy cell C1 down to the end of the list the done. NB it will automatically update if Excel is set to Automatic calculation, else press F9 to calculate the sheet.

The text in column M to O change the font colour to white; so it is not visible: if necessary.

Let me know it this is what you were thinking.

Jamie McMillan

 

[Gmax]

Thank you very much for your time !

Unfortunately it is not what I was looking for and I am sorry if what I triyed to explain what confusing !

In the picture below, you will find what I am trying to do, I don't know if that is possible to do or maybe it would require a bunch of formulas but that is the problem I am trying to solve.

As you can see, as soon as there is no information on what is present in a cell of column B, the "count" of employee starts back from the first one.
The column C is only here to show the link between an empty cell and the first employee.

I am sorry for wasting your time, I had not the possibiblity to post a picture yesterday, I hope that picture makes my problem clearer!

Again, thank you a lot for your time !

 

[Jamie McMillan]

Hi,

You explained it OK. I thought about that; but I couldn't think of why you would want it like that.

I will have another look in about 30 minutes.

Jamie

 

[Jamie McMillan]

Hello,

Sorry; but I can't get it.

I will try another idea tomorrow.

Jamie

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	Name 1	1	Name 1
2	Name 2	1	Name 2
3	Name 3	1	Name 3
4	Name 4	1	Name 4
5	Name 5		Name 1
6	Name 6	1	Name 2
7	Name 7	1	Name 3
8	Name 8	1	Name 4
9	Name 9	1	Name 5
10	Name 10	1	Name 6
11	Name 11	1	Name 7
12	Name 12		Name 1
13	Name 13	1	Name 2
14	Name 14	1	Name 3
Data

Cell Formulas
Range	Formula
C1:C14	C1	=INDEX($A$1:$A$18,ROW()-IFERROR(LOOKUP(2,1/(B$1:B1=""),ROW(B$1:B1)),1)+1)

 

[Gmax]

That is it !
Thank you very much to both of you !

 

[Fluff]

Glad we could help & thanks for the feedback.