Help with Index, small, concatenate formula.

[Russk68]

Hi all,
I have a column with names and another column with jobs. I need the result of the name and the job in the same cell in a condensed list.
Example:
Column B is the list of Names, Column C is the list of jobs.
B1="" C1=""
B2="Name1" C2="Job1"
B3=""C3=""
B4=Name2" C4="Job2"

The results are in B100 with "/" added.
B100="Name1/Job1"
B101="Name2/Job2"

I need to drag the formula across and down from B100 be to get results from column C and so on.
Column A is anchored.

As always:
THANK YOU!!!!!!!!

 

[James006]

Hi,

In cell B100, you can test :

=OFFSET($B$2,INT(INT(ROW()-100))*2,0)&"/"&OFFSET($C$2,INT(INT(ROW()-100))*2,0)

Hope his will help

 

[Russk68]

Hi James
The formula works exactly from my example but it doesn't seem to be dynamic. I need to make a correction. I meant to say that column B would be anchored and the formula would be in C100.

In my example above, I put "Name1" and "Job1" in row 1 and "Name2" and "Job2" in row 3 and they did not appear in the results. I tried to adjust your formula but did not succeed.

To be more specific as I should have been, B1:1000 are names that are anchored in the formula. C1:C1000 are jobs. The formula for column C will be C1056 and dragged down to C1106. I will then like to drag the formulas from C1056:C1106 across.

I'm usually not this specific because I try to keep things simple and adjust formulas as I need.

I appreciate your time and would appreciate it if you could make these adjustments.

Thank you!

Russ

 

[James006]

Hi,

Can you expand on ' How Dynamic ' the formula should be ... :wink:

 

[Russk68]

Sure
A name can be entered anywhere in B1:B1000
A job that the name is assigned to will be in the same row in column C.
There can be multiple entries.
The formula will start in cell C1056. It will show the first result. C1057 will show the 2nd result. The results column will be go to C1106. Column C represents the day of week.
If there are 5 entries spread out over many rows, (spaces in between entries) the formula needs to skip the spaces.
I will then drag formulas C1056:C1106 to the right to show a 2 year time period.
Column B needs to be anchored in the formula.
I hope this helps.
I appreciate your time!

 

[James006]

Hello,

To answer your question, you will need

1. To create a Named range such as ' TestRng ' to define your range B1:B1000

and

2. in your destination cell C1056, an Array Formula :

=IF(ROWS($1:1)<=COUNTA(TestRng),INDEX(TestRng&" / "&OFFSET(TestRng,0,1),SMALL(IF(TestRng<>"",ROW(TestRng)-MIN(ROW(TestRng))+1),ROWS($1:1))),"")

which will both skip all the Blank Rows and concatenate data in ranges B1:B1000 with C1:C1000

Hope this will help