Formula need or VB Code

[uswyne]

DATA FILE

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Grn[/TD]
[TD]Name[/TD]
[TD]Discount %[/TD]
[TD]Class[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]AAA[/TD]
[TD]100[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]BBB[/TD]
[TD]100[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]CCC[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]DDD[/TD]
[TD]100[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]005[/TD]
[TD]EEE[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Concession[/TD]
[TD]i want to maintain below list just enter 100 in this cell[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sr. No.[/TD]
[TD]GRN[/TD]
[TD]Name[/TD]
[TD]Class[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]001[/TD]
[TD]AAA[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]002[/TD]
[TD]BBB[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]004[/TD]
[TD]DDD[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

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