Extraction of Data based on dynamic criteria

[thespardian]

I simply want a dynamic way to group the above data . if there is empty cell in the column J (Centre) then ignore the whole row otherwise extract the data groups as per below format.

Any help will be highly appreciated.
Here is the link for excel file
Question123.xlsx

 

[thespardian]

I clicked the button and I don't see anything wrong on the Result sheet. What was not correct? I probably missed something.

Using button, It fetch only 2 line for Centre ABC and zero line for DEF whereas the correct results are 10 line for ABC and three line for DEF. If you run the code by clicking Developer > Visual Basic> Module1 and hit the play button. You'll see the difference.

 

[Zot]

Using button, It fetch only 2 line for Centre ABC and zero line for DEF whereas the correct results are 10 line for ABC and three line for DEF. If you run the code by clicking Developer > Visual Basic> Module1 and hit the play button. You'll see the difference.

This is what I get when I clicked

Zot.xlsm
	A	B	C	D	E	F	G	H	I
5	Sr.	Date	Cashier	Bill #	Customer Name	Product Code	Amount	Tax	Net
6	ABC
7	185	44195	By Letter	10088	Jhon Smith	VI-55	0	500	D
8	185	44195	By Letter	10088	Jhon Smith	B01152	0	967	D
9	185	44195	By Letter	10088	Jhon Smith	C01643	0	300	D
10	185	44195	By Letter	10088	Jhon Smith	C01611	0	150	D
11	185	44195	By Letter	10088	Jhon Smith	A01151-2	33560	0	D
12	185	44195	By Letter	10088	Jhon Smith	A01270-38-2	3356	0	D
13	185	44195	By Letter	10088	Jhon Smith	A01270-26-2	2596	0	D
14	185	44195	By Letter	10088	Jhon Smith	A01227-2	9498	0	D
15	185	44195	By Letter	10088	Jhon Smith	A01203	5000	0	D
16	185	44195	By Letter	10088	Jhon Smith	A01270-36-2	3356	0	D
17
18	DEF
19	185	44195	By Letter	10088	Jhon Smith	A01270-34-2	2646	0	D
20	185	44195	By Letter	10088	Jhon Smith	A01202-2	7010	0	D
21	185	44195	By Letter	10088	Jhon Smith	A01270-37-2	3356	0	D
Result

 

[maabadi]

Please Test Pivot table Method Post #7 & #8 . it is very well for you. Go exactly with instructions,

 

[thespardian]

This is what I get when I clicked

Zot.xlsm
	A	B	C	D	E	F	G	H	I
5	Sr.	Date	Cashier	Bill #	Customer Name	Product Code	Amount	Tax	Net
6	ABC
7	185	44195	By Letter	10088	Jhon Smith	VI-55	0	500	D
8	185	44195	By Letter	10088	Jhon Smith	B01152	0	967	D
9	185	44195	By Letter	10088	Jhon Smith	C01643	0	300	D
10	185	44195	By Letter	10088	Jhon Smith	C01611	0	150	D
11	185	44195	By Letter	10088	Jhon Smith	A01151-2	33560	0	D
12	185	44195	By Letter	10088	Jhon Smith	A01270-38-2	3356	0	D
13	185	44195	By Letter	10088	Jhon Smith	A01270-26-2	2596	0	D
14	185	44195	By Letter	10088	Jhon Smith	A01227-2	9498	0	D
15	185	44195	By Letter	10088	Jhon Smith	A01203	5000	0	D
16	185	44195	By Letter	10088	Jhon Smith	A01270-36-2	3356	0	D
17
18	DEF
19	185	44195	By Letter	10088	Jhon Smith	A01270-34-2	2646	0	D
20	185	44195	By Letter	10088	Jhon Smith	A01202-2	7010	0	D
21	185	44195	By Letter	10088	Jhon Smith	A01270-37-2	3356	0	D
Result

I was very reluctant to ask this follow up question. Because the codes were working fine. It may be some issue with my MS Excel.
Thanks a lot your guidance, time and patience with me. Stay blessed.

 

[Zot]

I was very reluctant to ask this follow up question. Because the codes were working fine. It may be some issue with my MS Excel.
Thanks a lot your guidance, time and patience with me. Stay blessed.

It is strange indeed. I'm using 2016 too.

Keep update if you get it to work or find another solution.

 

[fadee2]

Still not working for me. I am attaching the file. Can you please guide me where am i wrong?
Fadee2.xlsm

In my reply (Post # 6) I mentioned

assuming you have list of centers in column N

means you have to have a list of all your centers in column N, starting from cell N14. The code filters your data based on values in column N, and copy/paste relevant data to your desired result worksheet, one by one.

after doing the above try this

Sub abc()
Dim myrange As Range
Application.ScreenUpdating = False
Sheets("DATA").Activate
    LC = Cells(11, Columns.Count).End(xlToLeft).Column
    lr = Cells(Rows.Count, 1).End(xlUp).Row
   
ActiveSheet.Range("a11:j" & lr).AutoFilter
centerlr = Cells(Rows.Count, 14).End(xlUp).Row

For y = 12 To centerlr
    Sheets("DATA").Activate
    Range("a11:j" & lr).AutoFilter
    myvalue = Cells(y, 14)
    Range("A11:j" & lr).AutoFilter Field:=10, Criteria1:=myvalue
    
    Set myrange = Sheets("DATA").Range("a12:i" & lr)
    myrange.SpecialCells(xlCellTypeVisible).Copy

    With Sheets("DESIRED RESULT FORMAT")
        Sheets("DESIRED RESULT FORMAT").Activate
        If .Cells(5, 1) = "" Then
        .Cells(5, 1) = "S. NO"
        .Cells(5, 2) = "Date"
        .Cells(5, 3) = "Cashier"
        .Cells(5, 4) = "Bill #"
        .Cells(5, 5) = "Customer Name"
        .Cells(5, 6) = "Product Code"
        .Cells(5, 7) = "Amount"
        .Cells(5, 8) = "Tax"
        .Cells(5, 9) = "Net"
        End If
        destlr = Cells(Rows.Count, 1).End(xlUp).Row
        Sheets("DESIRED RESULT FORMAT").Range("a" & destlr + 1) = myvalue
        Sheets("DESIRED RESULT FORMAT").Range("a" & destlr + 2).PasteSpecial Paste:=xlPasteValues
    End With



Next y
Application.ScreenUpdating = True

End Sub

hope this helps....