Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting blocks of data
this is a sample of a large block of data I have. A block of data is
represented by the letter A until the next cell is Blank. For E.G. row 1 has 2 blocks of data. the 1st block is row1, col3 and 4 and the next block is column 9. row 2 has 2 blocks. Col3,col4 and col5 is the 1st block and column 8 & 9 is the 2nd block. Row 3 has 1 block only. column 6&7 Row 4 has 0 blocks and row 5 has 1 block because all the A's are in adjacent cells. 1 2 3 4 5 6 7 8 9 Row 1 A A A Row 2 A A A A A Row 3 A A Row 4 Row 5 A A A A Thank you all for your anticipated support in this again!! I have to count each block of data per row. E.G. Row 1 will have 2 blocks of data |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting blocks of data
Assuming your data is in the first 9 columns (A:I) as show, use this formula
on Row 1 to report how many "blocks" exist in Row 1 and copy the formula down as needed... =SUMPRODUCT((A1:H1<"")*(B1:I1="")) -- Rick (MVP - Excel) "tom_mcd" wrote in message ... this is a sample of a large block of data I have. A block of data is represented by the letter A until the next cell is Blank. For E.G. row 1 has 2 blocks of data. the 1st block is row1, col3 and 4 and the next block is column 9. row 2 has 2 blocks. Col3,col4 and col5 is the 1st block and column 8 & 9 is the 2nd block. Row 3 has 1 block only. column 6&7 Row 4 has 0 blocks and row 5 has 1 block because all the A's are in adjacent cells. 1 2 3 4 5 6 7 8 9 Row 1 A A A Row 2 A A A A A Row 3 A A Row 4 Row 5 A A A A Thank you all for your anticipated support in this again!! I have to count each block of data per row. E.G. Row 1 will have 2 blocks of data |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting blocks of data
This macro should do what you asked. I had to make several assumptions. I
assumed that you don't have a header row and you want to scan all columns for all rows that have data. I also didn't know what you wanted to do with the "blocks of data count". So I assumed you wanted to place it in a cell next to the last column with data in your used range. Hope this helps! If so, let me know, click "YES" below. Sub CountBlocks() Dim LastRow As Long Dim LastColumn As Long Dim rw As Long Dim cell As Range Dim Blocks As Long LastRow = Sheets("Sheet1").UsedRange.Rows.Count LastColumn = Sheets("Sheet1").UsedRange.Columns.Count For rw = 1 To LastRow For Each cell In Range(Cells(rw, "B"), Cells(rw, LastColumn)) If Not IsEmpty(cell) And IsEmpty(cell.Offset(, 1)) Then Blocks = Blocks + 1 End If Next cell Cells(rw, LastColumn + 1).Value = Blocks Blocks = 0 Next rw End Sub -- Cheers, Ryan "tom_mcd" wrote: this is a sample of a large block of data I have. A block of data is represented by the letter A until the next cell is Blank. For E.G. row 1 has 2 blocks of data. the 1st block is row1, col3 and 4 and the next block is column 9. row 2 has 2 blocks. Col3,col4 and col5 is the 1st block and column 8 & 9 is the 2nd block. Row 3 has 1 block only. column 6&7 Row 4 has 0 blocks and row 5 has 1 block because all the A's are in adjacent cells. 1 2 3 4 5 6 7 8 9 Row 1 A A A Row 2 A A A A A Row 3 A A Row 4 Row 5 A A A A Thank you all for your anticipated support in this again!! I have to count each block of data per row. E.G. Row 1 will have 2 blocks of data |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting blocks of data
Put in q2array enter using CSEcopy down
=SUM((("A"=C2:P2)-("A"=OFFSET(C2:P2,,1))=1)+0) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "tom_mcd" wrote in message ... this is a sample of a large block of data I have. A block of data is represented by the letter A until the next cell is Blank. For E.G. row 1 has 2 blocks of data. the 1st block is row1, col3 and 4 and the next block is column 9. row 2 has 2 blocks. Col3,col4 and col5 is the 1st block and column 8 & 9 is the 2nd block. Row 3 has 1 block only. column 6&7 Row 4 has 0 blocks and row 5 has 1 block because all the A's are in adjacent cells. 1 2 3 4 5 6 7 8 9 Row 1 A A A Row 2 A A A A A Row 3 A A Row 4 Row 5 A A A A Thank you all for your anticipated support in this again!! I have to count each block of data per row. E.G. Row 1 will have 2 blocks of data |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting blocks of 1's | Excel Worksheet Functions | |||
Count Blocks of Data | Excel Worksheet Functions | |||
averaging particular blocks of data | Excel Discussion (Misc queries) | |||
RangeName blocks of data | Excel Programming | |||
Copy Blocks Of Data | Excel Discussion (Misc queries) |