Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 21st 05, 04:05 PM posted to microsoft.public.excel.worksheet.functions
LOU
 
Posts: n/a
Default 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


  #2   Report Post  
Old November 21st 05, 04:17 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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



  #3   Report Post  
Old November 21st 05, 04:47 PM posted to microsoft.public.excel.worksheet.functions
LOU
 
Posts: n/a
Default 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




  #4   Report Post  
Old November 21st 05, 05:04 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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
news
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






  #5   Report Post  
Old November 21st 05, 07:42 PM posted to microsoft.public.excel.worksheet.functions
LOU
 
Posts: n/a
Default 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
news
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









  #6   Report Post  
Old November 21st 05, 07:43 PM posted to microsoft.public.excel.worksheet.functions
LOU
 
Posts: n/a
Default 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
news
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







  #7   Report Post  
Old November 21st 05, 08:04 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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
news
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











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How change dimensions of data label text box in pie chart? Gouden Willem Charts and Charting in Excel 3 March 7th 06 12:11 PM
combining 2 pieces of text from 2 columns alexy Excel Worksheet Functions 3 September 15th 05 10:35 AM
Do I need a sumif or sum of a vlookup formula? PeterB Excel Worksheet Functions 0 June 1st 05 12:23 PM
Autofitting a row Josephine Excel Discussion (Misc queries) 2 March 3rd 05 03:37 PM
Sort or Filter option? Mcobra41 Excel Worksheet Functions 3 February 23rd 05 07:22 PM


All times are GMT +1. The time now is 01:43 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017