I have an Excel spreadsheet that has as a header row: #, Date, Per0, Per1,
Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number, date,
then for Per0 through Per7 may have a T in the cell. Sometimes the cell may
have another character other than a T. What I need to do is take the T and
change it to, let's say a 3 [for Per3] then add a comma and continue to the
next cell [if there is another T] and add, hypothetically a 5 for Per5... and
so on until the end of the rows with a number and date. If anyone can help
me, I'd appreciate it. Hope you have a great day and a good weekend....
ILoveMyCorgi

"ILoveMyCorgi" wrote:

> I have an Excel spreadsheet that has as a header row: #, Date, Per0, Per1,
> Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number, date,
> then for Per0 through Per7 may have a T in the cell. Sometimes the cell may
> have another character other than a T. What I need to do is take the T and
> change it to, let's say a 3 [for Per3] then add a comma and continue to the
> next cell [if there is another T] and add, hypothetically a 5 for Per5... and
> so on until the end of the rows with a number and date.

The outcome shoudl look like:

12345 09/25/2008 3,5
If anyone can help
> me, I'd appreciate it. Hope you have a great day and a good weekend....
> ILoveMyCorgi

Do you want the outcome to replace the data in the original sheet, or
populate a new sheet?

What do you want to do if there are no "t"'s in the row?

It seems you could just use the find/replace function.

> > What I need to do is take the T and
> > change it to, let's say a 3 [for Per3] then add a comma and continue tothe
> > next cell [if there is another T] and add, hypothetically a 5 for Per5.... and
> > so on until the end of the rows with a number and date. *
>
> The outcome shoudl look like:
>
> 12345 * 09/25/2008 3,5

It is unclear whether where you want this output at. Since you posted to the
programming group, one could assume you are looking for VBA code; but, if
you are, where do you want the outcome placed... in an array, in another
cell, somewhere else? Personally, I think you are looking for a worksheet
formula (microsoft.public.excel.worksheet.functions would have been a better
newsgroup to use if that is the case); but again, where do you want the
output... in a new column or to replace the contents of C:J for each row?
Anyway, assuming you want a formula, and using a new column (same row as the
data), this formula can be used as a starting point...

=A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")&
" "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF(
G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T",
7,""))," ",",")

Copy it down as needed.

--
Rick (MVP - Excel)


"ILoveMyCorgi" <(E-Mail Removed)> wrote in message
news:F53CD5AF-C2E0-473D-9B30-(E-Mail Removed)...
>
>
> "ILoveMyCorgi" wrote:
>
>> I have an Excel spreadsheet that has as a header row: #, Date, Per0,
>> Per1,
>> Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number,
>> date,
>> then for Per0 through Per7 may have a T in the cell. Sometimes the cell
>> may
>> have another character other than a T. What I need to do is take the T
>> and
>> change it to, let's say a 3 [for Per3] then add a comma and continue to
>> the
>> next cell [if there is another T] and add, hypothetically a 5 for Per5...
>> and
>> so on until the end of the rows with a number and date.
>
> The outcome shoudl look like:
>
> 12345 09/25/2008 3,5
> If anyone can help
>> me, I'd appreciate it. Hope you have a great day and a good weekend....
>> ILoveMyCorgi

It could be on a new sheet so that the outcome would look something like:
1234 09/25/2008 3,5
or
56789 09/24/2008 1,2,3,4,5,6,7

"dan dungan" wrote:

> Do you want the outcome to replace the data in the original sheet, or
> populate a new sheet?
>
> What do you want to do if there are no "t"'s in the row?
>
> It seems you could just use the find/replace function.
>
> > > What I need to do is take the T and
> > > change it to, let's say a 3 [for Per3] then add a comma and continue to the
> > > next cell [if there is another T] and add, hypothetically a 5 for Per5.... and
> > > so on until the end of the rows with a number and date.
> >
> > The outcome shoudl look like:
> >
> > 12345 09/25/2008 3,5
>
>
>

Thansk for the formula, but I was hoping for VBA code and have the output on
a separate worksheet within the file. Output would be first column: number,
second: date, third column: 2, 3, 5... etc.

"Rick Rothstein" wrote:

> It is unclear whether where you want this output at. Since you posted to the
> programming group, one could assume you are looking for VBA code; but, if
> you are, where do you want the outcome placed... in an array, in another
> cell, somewhere else? Personally, I think you are looking for a worksheet
> formula (microsoft.public.excel.worksheet.functions would have been a better
> newsgroup to use if that is the case); but again, where do you want the
> output... in a new column or to replace the contents of C:J for each row?
> Anyway, assuming you want a formula, and using a new column (same row as the
> data), this formula can be used as a starting point...
>
> =A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")&
> " "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF(
> G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T",
> 7,""))," ",",")
>
> Copy it down as needed.
>
> --
> Rick (MVP - Excel)
>
>
> "ILoveMyCorgi" <(E-Mail Removed)> wrote in message
> news:F53CD5AF-C2E0-473D-9B30-(E-Mail Removed)...
> >
> >
> > "ILoveMyCorgi" wrote:
> >
> >> I have an Excel spreadsheet that has as a header row: #, Date, Per0,
> >> Per1,
> >> Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number,
> >> date,
> >> then for Per0 through Per7 may have a T in the cell. Sometimes the cell
> >> may
> >> have another character other than a T. What I need to do is take the T
> >> and
> >> change it to, let's say a 3 [for Per3] then add a comma and continue to
> >> the
> >> next cell [if there is another T] and add, hypothetically a 5 for Per5...
> >> and
> >> so on until the end of the rows with a number and date.
> >
> > The outcome shoudl look like:
> >
> > 12345 09/25/2008 3,5
> > If anyone can help
> >> me, I'd appreciate it. Hope you have a great day and a good weekend....
> >> ILoveMyCorgi
>
>

Thanks. This will work, but I was looking for a VBA solution so that when I
extract the data, the clerk can run the extract.

"Rick Rothstein" wrote:

> It is unclear whether where you want this output at. Since you posted to the
> programming group, one could assume you are looking for VBA code; but, if
> you are, where do you want the outcome placed... in an array, in another
> cell, somewhere else? Personally, I think you are looking for a worksheet
> formula (microsoft.public.excel.worksheet.functions would have been a better
> newsgroup to use if that is the case); but again, where do you want the
> output... in a new column or to replace the contents of C:J for each row?
> Anyway, assuming you want a formula, and using a new column (same row as the
> data), this formula can be used as a starting point...
>
> =A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")&
> " "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF(
> G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T",
> 7,""))," ",",")
>
> Copy it down as needed.
>
> --
> Rick (MVP - Excel)
>
>
> "ILoveMyCorgi" <(E-Mail Removed)> wrote in message
> news:F53CD5AF-C2E0-473D-9B30-(E-Mail Removed)...
> >
> >
> > "ILoveMyCorgi" wrote:
> >
> >> I have an Excel spreadsheet that has as a header row: #, Date, Per0,
> >> Per1,
> >> Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number,
> >> date,
> >> then for Per0 through Per7 may have a T in the cell. Sometimes the cell
> >> may
> >> have another character other than a T. What I need to do is take the T
> >> and
> >> change it to, let's say a 3 [for Per3] then add a comma and continue to
> >> the
> >> next cell [if there is another T] and add, hypothetically a 5 for Per5...
> >> and
> >> so on until the end of the rows with a number and date.
> >
> > The outcome shoudl look like:
> >
> > 12345 09/25/2008 3,5
> > If anyone can help
> >> me, I'd appreciate it. Hope you have a great day and a good weekend....
> >> ILoveMyCorgi
>
>

How about this macro then (change the two worksheet references in the two
With Worksheets(...) statements to the worksheets where your data is and
where you output should go...

Sub ProcessPerColumns()
Dim X As Long
Dim Z As Long
Dim LenOutput As Long
Dim LastRow As Long
Dim LastOutputRow As Long
Dim Output As String
With Worksheets("DataSheet")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow
Output = .Cells(X, "A") & " " & .Cells(X, "B") & " "
LenOutput = Len(Output)
For Z = 3 To 10 'Columns C thru J
If .Cells(X, Z).Value = "T" Then
If Len(Output) > LenOutput Then Output = Output & ", "
Output = Output & .Cells(X, Z).Value
End If
Next
With Worksheets("OutputSheet")
LastOutputRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(LastOutputRow + 1, "A").Value = Output
End With
Next
End With
End Sub

--
Rick (MVP - Excel)


"ILoveMyCorgi" <(E-Mail Removed)> wrote in message
news:60EAD922-B4D6-401B-AA09-(E-Mail Removed)...
> Thanks. This will work, but I was looking for a VBA solution so that when
> I
> extract the data, the clerk can run the extract.
>
> "Rick Rothstein" wrote:
>
>> It is unclear whether where you want this output at. Since you posted to
>> the
>> programming group, one could assume you are looking for VBA code; but, if
>> you are, where do you want the outcome placed... in an array, in another
>> cell, somewhere else? Personally, I think you are looking for a worksheet
>> formula (microsoft.public.excel.worksheet.functions would have been a
>> better
>> newsgroup to use if that is the case); but again, where do you want the
>> output... in a new column or to replace the contents of C:J for each row?
>> Anyway, assuming you want a formula, and using a new column (same row as
>> the
>> data), this formula can be used as a starting point...
>>
>> =A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")&
>> " "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF(
>> G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T",
>> 7,""))," ",",")
>>
>> Copy it down as needed.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "ILoveMyCorgi" <(E-Mail Removed)> wrote in message
>> news:F53CD5AF-C2E0-473D-9B30-(E-Mail Removed)...
>> >
>> >
>> > "ILoveMyCorgi" wrote:
>> >
>> >> I have an Excel spreadsheet that has as a header row: #, Date, Per0,
>> >> Per1,
>> >> Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number,
>> >> date,
>> >> then for Per0 through Per7 may have a T in the cell. Sometimes the
>> >> cell
>> >> may
>> >> have another character other than a T. What I need to do is take the
>> >> T
>> >> and
>> >> change it to, let's say a 3 [for Per3] then add a comma and continue
>> >> to
>> >> the
>> >> next cell [if there is another T] and add, hypothetically a 5 for
>> >> Per5...
>> >> and
>> >> so on until the end of the rows with a number and date.
>> >
>> > The outcome shoudl look like:
>> >
>> > 12345 09/25/2008 3,5
>> > If anyone can help
>> >> me, I'd appreciate it. Hope you have a great day and a good
>> >> weekend....
>> >> ILoveMyCorgi
>>
>>

Thanks. I copied the code and tried to run but I got a run-time error '9':
Subscript out of range.

"Rick Rothstein" wrote:

> How about this macro then (change the two worksheet references in the two
> With Worksheets(...) statements to the worksheets where your data is and
> where you output should go...
>
> Sub ProcessPerColumns()
> Dim X As Long
> Dim Z As Long
> Dim LenOutput As Long
> Dim LastRow As Long
> Dim LastOutputRow As Long
> Dim Output As String
> With Worksheets("DataSheet")
> LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> For X = 2 To LastRow
> Output = .Cells(X, "A") & " " & .Cells(X, "B") & " "
> LenOutput = Len(Output)
> For Z = 3 To 10 'Columns C thru J
> If .Cells(X, Z).Value = "T" Then
> If Len(Output) > LenOutput Then Output = Output & ", "
> Output = Output & .Cells(X, Z).Value
> End If
> Next
> With Worksheets("OutputSheet")
> LastOutputRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> .Cells(LastOutputRow + 1, "A").Value = Output
> End With
> Next
> End With
> End Sub
>
> --
> Rick (MVP - Excel)
>
>
> "ILoveMyCorgi" <(E-Mail Removed)> wrote in message
> news:60EAD922-B4D6-401B-AA09-(E-Mail Removed)...
> > Thanks. This will work, but I was looking for a VBA solution so that when
> > I
> > extract the data, the clerk can run the extract.
> >
> > "Rick Rothstein" wrote:
> >
> >> It is unclear whether where you want this output at. Since you posted to
> >> the
> >> programming group, one could assume you are looking for VBA code; but, if
> >> you are, where do you want the outcome placed... in an array, in another
> >> cell, somewhere else? Personally, I think you are looking for a worksheet
> >> formula (microsoft.public.excel.worksheet.functions would have been a
> >> better
> >> newsgroup to use if that is the case); but again, where do you want the
> >> output... in a new column or to replace the contents of C:J for each row?
> >> Anyway, assuming you want a formula, and using a new column (same row as
> >> the
> >> data), this formula can be used as a starting point...
> >>
> >> =A2&" "&TEXT(B2,"mm/dd/yyyy")&" "&SUBSTITUTE(TRIM(IF(C2="T","0","")&
> >> " "&IF(D2="T",1,"")&" "&IF(E2="T",2,"")&" "&IF(F2="T",3,"")&" "&IF(
> >> G2="T",4,"")&" "&IF(H2="T",5,"")&" "&IF(I2="T",6,"")&" "&IF(J2="T",
> >> 7,""))," ",",")
> >>
> >> Copy it down as needed.
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >>
> >> "ILoveMyCorgi" <(E-Mail Removed)> wrote in message
> >> news:F53CD5AF-C2E0-473D-9B30-(E-Mail Removed)...
> >> >
> >> >
> >> > "ILoveMyCorgi" wrote:
> >> >
> >> >> I have an Excel spreadsheet that has as a header row: #, Date, Per0,
> >> >> Per1,
> >> >> Per2, Per3, Per4, Per5, Per6, Per7. The rows below have the number,
> >> >> date,
> >> >> then for Per0 through Per7 may have a T in the cell. Sometimes the
> >> >> cell
> >> >> may
> >> >> have another character other than a T. What I need to do is take the
> >> >> T
> >> >> and
> >> >> change it to, let's say a 3 [for Per3] then add a comma and continue
> >> >> to
> >> >> the
> >> >> next cell [if there is another T] and add, hypothetically a 5 for
> >> >> Per5...
> >> >> and
> >> >> so on until the end of the rows with a number and date.
> >> >
> >> > The outcome shoudl look like:
> >> >
> >> > 12345 09/25/2008 3,5
> >> > If anyone can help
> >> >> me, I'd appreciate it. Hope you have a great day and a good
> >> >> weekend....
> >> >> ILoveMyCorgi
> >>
> >>
>
>

Hi Rick and OP

I'm trying to understand how to convert the questions into the proper
logic, so I'm examining your code, Thanks for sharing your knowledge,
Rick.

I copied the code and it ran after I changed the names of the
worksheets to
DataSheet and OutputSheet.

Also, It didn't return any values but the number and date for each row
until I changed

the line

If .Cells(X, Z).Value = "T" Then

to

If .Cells(X, Z).Value = "t" Then

because I had used lowercase t.

I made two more changes because the code was returning data like

45879 10/10/2007 t
45880 10/11/2007 t
45881 10/12/2007 t,t

First, I changed
Output = Output & .Cells(X, Z).Value

to

Output = Output & Z


But the numbers didn't match Per0, Per1, etc

so i added

Output = Output & (Z - 3)

That's my experience with this code,

Dan