Formula need or VB Code

[uswyne]

DATA FILE

Grn	Name	Discount %	Class
001	AAA	100	5
002	BBB	100	6
003	CCC		4
004	DDD	100	3
005	EEE		1

<tbody>
</tbody>

Concession

i want to maintain below list just enter 100 in this cell

<tbody>
</tbody>

Sr. No.	GRN	Name	Class
01	001	AAA	5
02	002	BBB	6
03	004	DDD	3

<tbody>
</tbody>

Kindly Help

 

[SteveO59L]

would you like your 2nd table to be on a new sheet?, or on the same sheet?

does your 2nd table get overwritten each time?

 

[uswyne]

would you like your 2nd table to be on a new sheet?, or on the same sheet?

does your 2nd table get overwritten each time?

Concession list would be in new sheet and list shows what ever in the data file, data file could be changed as per need.

thanks

 

[SteveO59L]

something along the lines of

Dim c As Range, rng
Dim lr As Long
Dim ser_no As Integer
Dim grn As String
Dim name As String
Dim class As String

Sheets("sheet2").Activate
lr = Range("a" & Rows.Count).End(xlUp).Row
Range("a2:d" & lr).ClearContents
Sheets("sheet1").Activate
lr = Range("a" & Rows.Count).End(xlUp).Row
Set rng = Range("c2:c" & lr)
For Each c In rng
If c.Value = 100 Then
ser_no = ser_no + 1
grn = c.Offset(0, -2).Value
name = c.Offset(0, -1).Value
class = c.Offset(0, 1).Value
Sheets("sheet2").Activate
lr = Range("a" & Rows.Count).End(xlUp).Row + 1
Range("a" & lr) = ser_no
Range("b" & lr) = grn
Range("c" & lr) = name
Range("d" & lr) = class
Sheets("sheet1").Activate
End If
Next c