How would a make a table like this?

[sarman]

Hi there. This is m first post here on these forums.

My question is a little hard to explain, so please bare with me. I have numerous sheets in one workbook, all containing the same table with different data. What I want to do on a separate "summary" sheet is find all the items that are marked "incomplete" and return the names of these items. Here is kind of how one sheet is set up (with a lot of other columns not shown):

Package -----Item---------------Status
a ---------------- 1 ---------------- incomplete
a ---------------- 2 ---------------- complete
a ---------------- 3 ---------------- incomplete
a ---------------- 4 ---------------- incomplete
b ---------------- 5 ---------------- complete
b ---------------- 6 ---------------- incomplete
b ---------------- 7 ---------------- incomplete

So on my summary sheet I would want a table to return the following:

Package--------Incomplete Items
a -------------------------1
--------------------------- 3
--------------------------- 4
b -------------------------6
---------------------------7

The next sheet may have package c, d, and e, and so on. I would like the one summary table to summarize every sheet in one table.

Sorry if this was confusing, hopefully someone can help. Thanks!

 

[venkat1926]

try the macro test

Dim pack As Range, r As Range, un As Range, cun As Range, filt As Range
Dim x
With Worksheets("sheet1")
Set pack = Range(.Range("A1"), .Range("A1").End(xlDown))
Set r = .Range("A1").CurrentRegion
Set un = .Range("a1").End(xlDown).Offset(5, 0)
pack.AdvancedFilter xlFilterCopy, , un, True
Set un = Range(un.Offset(1, 0), un.End(xlDown))
For Each cun In un
x = cun.Value
 r.AutoFilter field:=1, Criteria1:=x
r.AutoFilter field:=3, Criteria1:="incomplete"
r.Offset(1, 0).Resize(r.Rows.Count - 1, r.Columns.Count - 1).SpecialCells(xlCellTypeVisible).Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).PasteSpecial
End With
r.AutoFilter
Next cun
End With
With Worksheets("sheet2")
.Range("A1") = "package"
.Range("B1") = "incomplete items"
End With
End Sub

Sub undo()
With Worksheets("sheet1")
Range(.Range("a1").End(xlDown).Offset(1, 0), .Cells(Rows.Count, "A")).EntireRow.Delete
End With
With Worksheets("sheet2")
.Cells.Clear
End With
End Sub

 

[sarman]

I really do not know much about macros. Is there any chance you could explain this, or is that a lot to learn?

 

[venkat1926]

do you want to learn how to interpret macro or do you want to know how to RUN the macro. If you do not know how to run the macro I shall explain.

If you have run the macro and got the correct result it is ok. I shall explain the various statement in the macro.

please reply