ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Combining Text on statement (https://www.excelbanter.com/excel-worksheet-functions/56545-combining-text-statement.html)

LOU

Combining Text on statement
 
Hello,
I have a spreadsheet set as such,

Columns
A = Date
B = Employee (B2 THRU B116 , Column to house formula)
Columns C1 thru z1 have employee names
in C1 thru Z1 I have a 1 indicatiing they are assigned this day.

I would like the formula to review C1:Z1 and if there is a 1 in a column
bring back the employees name to that row in column B and combine them
together.

I hope this is clear, I need to stay away from a pivot table though, any
help would be great.

Thank you,
Lou


Bob Phillips

Combining Text on statement
 
Your example is a bit confusing.

Is this what you want

=COUNTIF(C2:Z2,1)

for the employee in B2

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LOU" wrote in message
...
Hello,
I have a spreadsheet set as such,

Columns
A = Date
B = Employee (B2 THRU B116 , Column to house formula)
Columns C1 thru z1 have employee names
in C1 thru Z1 I have a 1 indicatiing they are assigned this day.

I would like the formula to review C1:Z1 and if there is a 1 in a column
bring back the employees name to that row in column B and combine them
together.

I hope this is clear, I need to stay away from a pivot table though, any
help would be great.

Thank you,
Lou




LOU

Combining Text on statement
 
Bob,
Thank you, sorry for the confusion. I want it to bring back the persons name
from row 1,
Example..

A= Date B1= Employees C1= Bill D1=Jen E1=Lou
1/1/06 Bill, Jen 1 1
2/1/06 Bill, Lou 1
1
3/1/0 Jen, Lou 1
1

Thank you.
Lou
"Bob Phillips" wrote:

Your example is a bit confusing.

Is this what you want

=COUNTIF(C2:Z2,1)

for the employee in B2

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LOU" wrote in message
...
Hello,
I have a spreadsheet set as such,

Columns
A = Date
B = Employee (B2 THRU B116 , Column to house formula)
Columns C1 thru z1 have employee names
in C1 thru Z1 I have a 1 indicatiing they are assigned this day.

I would like the formula to review C1:Z1 and if there is a 1 in a column
bring back the employees name to that row in column B and combine them
together.

I hope this is clear, I need to stay away from a pivot table though, any
help would be great.

Thank you,
Lou





Bob Phillips

Combining Text on statement
 
I still don't think I get it.

What part of that are you trying to calculate, and what is being input?

Another guess.

C2: =IF(ISNUMBER(FIND(B$1,$B2)),1,"")

and copy down and acrooss

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LOU" wrote in message
...
Bob,
Thank you, sorry for the confusion. I want it to bring back the persons

name
from row 1,
Example..

A= Date B1= Employees C1= Bill D1=Jen E1=Lou
1/1/06 Bill, Jen 1 1
2/1/06 Bill, Lou 1
1
3/1/0 Jen, Lou 1
1

Thank you.
Lou
"Bob Phillips" wrote:

Your example is a bit confusing.

Is this what you want

=COUNTIF(C2:Z2,1)

for the employee in B2

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LOU" wrote in message
...
Hello,
I have a spreadsheet set as such,

Columns
A = Date
B = Employee (B2 THRU B116 , Column to house formula)
Columns C1 thru z1 have employee names
in C1 thru Z1 I have a 1 indicatiing they are assigned this day.

I would like the formula to review C1:Z1 and if there is a 1 in a

column
bring back the employees name to that row in column B and combine them
together.

I hope this is clear, I need to stay away from a pivot table though,

any
help would be great.

Thank you,
Lou







LOU

Combining Text on statement
 
Heya Bill thank you for your time and patience.

I want to bring the person Name from the HEADER row in the column which has
the employees.
Column A Column B Column C
Column D Column E
ROW 1 - HEADER ROW Date Employees Bill Jen
Lou
ROW 2 1/1/06 Bill, Jen 1
1
ROW 3 2/1/06 Bill, Lou 1
1
ROW 4 3/1/06 Jen, Lou
1 1

Column B will hold the formula which will bring back the results. If there
is a 1 in any cell from C2:E2 it will bring the names back from the header
row. Example above would be something like this in B2
If(c2=1,$c$1,if(d2=1,$d$1))

I hope this makes sense.
Thank you,
Lou

"Bob Phillips" wrote:

I still don't think I get it.

What part of that are you trying to calculate, and what is being input?

Another guess.

C2: =IF(ISNUMBER(FIND(B$1,$B2)),1,"")

and copy down and acrooss

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LOU" wrote in message
...
Bob,
Thank you, sorry for the confusion. I want it to bring back the persons

name
from row 1,
Example..

A= Date B1= Employees C1= Bill D1=Jen E1=Lou
1/1/06 Bill, Jen 1 1
2/1/06 Bill, Lou 1
1
3/1/0 Jen, Lou 1
1

Thank you.
Lou
"Bob Phillips" wrote:

Your example is a bit confusing.

Is this what you want

=COUNTIF(C2:Z2,1)

for the employee in B2

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LOU" wrote in message
...
Hello,
I have a spreadsheet set as such,

Columns
A = Date
B = Employee (B2 THRU B116 , Column to house formula)
Columns C1 thru z1 have employee names
in C1 thru Z1 I have a 1 indicatiing they are assigned this day.

I would like the formula to review C1:Z1 and if there is a 1 in a

column
bring back the employees name to that row in column B and combine them
together.

I hope this is clear, I need to stay away from a pivot table though,

any
help would be great.

Thank you,
Lou








LOU

Combining Text on statement
 
sorry, I am just inputting the "1"s under the names down there corresponding
column.......

Lou

"Bob Phillips" wrote:

I still don't think I get it.

What part of that are you trying to calculate, and what is being input?

Another guess.

C2: =IF(ISNUMBER(FIND(B$1,$B2)),1,"")

and copy down and acrooss

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LOU" wrote in message
...
Bob,
Thank you, sorry for the confusion. I want it to bring back the persons

name
from row 1,
Example..

A= Date B1= Employees C1= Bill D1=Jen E1=Lou
1/1/06 Bill, Jen 1 1
2/1/06 Bill, Lou 1
1
3/1/0 Jen, Lou 1
1

Thank you.
Lou
"Bob Phillips" wrote:

Your example is a bit confusing.

Is this what you want

=COUNTIF(C2:Z2,1)

for the employee in B2

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LOU" wrote in message
...
Hello,
I have a spreadsheet set as such,

Columns
A = Date
B = Employee (B2 THRU B116 , Column to house formula)
Columns C1 thru z1 have employee names
in C1 thru Z1 I have a 1 indicatiing they are assigned this day.

I would like the formula to review C1:Z1 and if there is a 1 in a

column
bring back the employees name to that row in column B and combine them
together.

I hope this is clear, I need to stay away from a pivot table though,

any
help would be great.

Thank you,
Lou








Bob Phillips

Combining Text on statement
 
I would use a UDF

Function Get(rng As Range)
Dim cell As Range
For Each cell In rng
If cell.Value = 1 Then
GetNames = GetNames & Cells(1, cell.Column) & ","
End If
Next cell
If GetNames < "" Then
GetNames = Left(GetNames, Len(GetNames) - 1)
End If
End Function


And use in B2 like this

=GetNames(C2:E2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LOU" wrote in message
...
Heya Bill thank you for your time and patience.

I want to bring the person Name from the HEADER row in the column which

has
the employees.
Column A Column B Column C
Column D Column E
ROW 1 - HEADER ROW Date Employees Bill

Jen
Lou
ROW 2 1/1/06 Bill, Jen 1
1
ROW 3 2/1/06 Bill, Lou 1
1
ROW 4 3/1/06 Jen, Lou
1 1

Column B will hold the formula which will bring back the results. If there
is a 1 in any cell from C2:E2 it will bring the names back from the header
row. Example above would be something like this in B2
If(c2=1,$c$1,if(d2=1,$d$1))

I hope this makes sense.
Thank you,
Lou

"Bob Phillips" wrote:

I still don't think I get it.

What part of that are you trying to calculate, and what is being input?

Another guess.

C2: =IF(ISNUMBER(FIND(B$1,$B2)),1,"")

and copy down and acrooss

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LOU" wrote in message
...
Bob,
Thank you, sorry for the confusion. I want it to bring back the

persons
name
from row 1,
Example..

A= Date B1= Employees C1= Bill D1=Jen E1=Lou
1/1/06 Bill, Jen 1 1
2/1/06 Bill, Lou 1
1
3/1/0 Jen, Lou 1
1

Thank you.
Lou
"Bob Phillips" wrote:

Your example is a bit confusing.

Is this what you want

=COUNTIF(C2:Z2,1)

for the employee in B2

--

HTH

RP
(remove nothere from the email address if mailing direct)


"LOU" wrote in message
...
Hello,
I have a spreadsheet set as such,

Columns
A = Date
B = Employee (B2 THRU B116 , Column to house formula)
Columns C1 thru z1 have employee names
in C1 thru Z1 I have a 1 indicatiing they are assigned this day.

I would like the formula to review C1:Z1 and if there is a 1 in a

column
bring back the employees name to that row in column B and combine

them
together.

I hope this is clear, I need to stay away from a pivot table

though,
any
help would be great.

Thank you,
Lou











All times are GMT +1. The time now is 04:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com