Group,
I have a spreadsheet that changes weekly. The spreadsheet can have
over a 1000 rows or just a few hundred. In column D, a cell may be
blank or if there is an entry will have a text value with a suffix
..checksum, like regioncode.checksum or rds.checksum. The prefix before
the (.) dot will always be lumped together once there is an entry. In
otherword, rds.checksum will never appear again after cell D7 once its
initial incidence (D3) is found, regioncode.checksum will never appear
again after D20 once its first incidence (D11) is found below. I want
to show on another worksheet called "Summary", the name of the variable
(ie: rds.checksum) in cell A2, its starting row (3) in cell B2, its
ending row (7) in cell C2 and so on for each group of .checksum
entries. Any help here would be awesome.

Cell:Contents
D1: < Blank
D2: < Blank
D3: rds.checksum
D4: rds.checksum
D5: rds.checksum
D6: rds.checksum
D7: rds.checksum
D8: < Blank
D9: < Blank
D10: < Blank
D11: regioncode.checksum
D12: regioncode.checksum
D13: regioncode.checksum
D14: regioncode.checksum
D15: regioncode.checksum
D16: regioncode.checksum
D17: regioncode.checksum
D18: regioncode.checksum
D19 regioncode.checksum
D20: regioncode.checksum
D21: < Blank
D22: < Blank
D23: < Blank
D24: < Blank
etc...........

Tony


--
ajocius
------------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=470579

will you have a list of the unique checksums in the summary sheet and you
want formulas to calculate the locations, or do you want a macro to build
the entire summary sheet include building the list of unique checksums?

--
Regards,
Tom Ogilvy

"ajocius" wrote in
message ...

Group,
I have a spreadsheet that changes weekly. The spreadsheet can have
over a 1000 rows or just a few hundred. In column D, a cell may be
blank or if there is an entry will have a text value with a suffix
checksum, like regioncode.checksum or rds.checksum. The prefix before
the (.) dot will always be lumped together once there is an entry. In
otherword, rds.checksum will never appear again after cell D7 once its
initial incidence (D3) is found, regioncode.checksum will never appear
again after D20 once its first incidence (D11) is found below. I want
to show on another worksheet called "Summary", the name of the variable
(ie: rds.checksum) in cell A2, its starting row (3) in cell B2, its
ending row (7) in cell C2 and so on for each group of .checksum
entries. Any help here would be awesome.

Cell:Contents
D1: < Blank
D2: < Blank
D3: rds.checksum
D4: rds.checksum
D5: rds.checksum
D6: rds.checksum
D7: rds.checksum
D8: < Blank
D9: < Blank
D10: < Blank
D11: regioncode.checksum
D12: regioncode.checksum
D13: regioncode.checksum
D14: regioncode.checksum
D15: regioncode.checksum
D16: regioncode.checksum
D17: regioncode.checksum
D18: regioncode.checksum
D19 regioncode.checksum
D20: regioncode.checksum
D21: < Blank
D22: < Blank
D23: < Blank
D24: < Blank
etc...........

Tony


--
ajocius
------------------------------------------------------------------------
ajocius's Profile:
http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=470579

Try this. Change Sheet1 to suit your source sheet name.
Sub findvariable()
rd = 2
For r = 2 To 1000
If Worksheets("Sheet1").Cells(r - 1, 4) = "" And
Worksheets("Sheet1").Cells(r, 4) < "" Then
Worksheets("Summary").Cells(rd, 1).Value =
Worksheets("Sheet1").Cells(r, 4).Value
Worksheets("Summary").Cells(rd, 2) = Worksheets("Sheet1").Cells(r,
4).Address
End If
If Worksheets("Sheet1").Cells(r - 1, 4) < "" And
Worksheets("Sheet1").Cells(r, 4) = "" Then
Worksheets("Summary").Cells(rd, 3) = Worksheets("Sheet1").Cells(r,
4).Address
rd = rd + 1
End If
Next r
End Sub


--
Ian
--
"ajocius" wrote in
message ...

Group,
I have a spreadsheet that changes weekly. The spreadsheet can have
over a 1000 rows or just a few hundred. In column D, a cell may be
blank or if there is an entry will have a text value with a suffix
.checksum, like regioncode.checksum or rds.checksum. The prefix before
the (.) dot will always be lumped together once there is an entry. In
otherword, rds.checksum will never appear again after cell D7 once its
initial incidence (D3) is found, regioncode.checksum will never appear
again after D20 once its first incidence (D11) is found below. I want
to show on another worksheet called "Summary", the name of the variable
(ie: rds.checksum) in cell A2, its starting row (3) in cell B2, its
ending row (7) in cell C2 and so on for each group of .checksum
entries. Any help here would be awesome.

Cell:Contents
D1: < Blank
D2: < Blank
D3: rds.checksum
D4: rds.checksum
D5: rds.checksum
D6: rds.checksum
D7: rds.checksum
D8: < Blank
D9: < Blank
D10: < Blank
D11: regioncode.checksum
D12: regioncode.checksum
D13: regioncode.checksum
D14: regioncode.checksum
D15: regioncode.checksum
D16: regioncode.checksum
D17: regioncode.checksum
D18: regioncode.checksum
D19 regioncode.checksum
D20: regioncode.checksum
D21: < Blank
D22: < Blank
D23: < Blank
D24: < Blank
etc...........

Tony


--
ajocius
------------------------------------------------------------------------
ajocius's Profile:
http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=470579

Tom,
I just need a list built with the unique name, and beginning/ending
rows. Everything else is done. What makes this problem so difficult
for me is that the prefix to the .checksum can be different from
Worksheet to Worksheet. Fortunately, the group of something.checksum
will always be unique in the spreadsheet and will be listed
consecutively. I hope that explains it.

Tony


--
ajocius
------------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=470579