Refer my attachment.
My data will be in Column B to H and Required format through VBA Codes as shown in Column J to P.
It should be in next sheet.
Look up the each vendor code in column B at once and their GSTR1 and GSTR3B whose date is maximum one ( newest one ) i.e.highlight in yellow colour and put up the Period against maximum date.
Can it be possible through VBA Codes or Pivot Table ?
VBA Codes of Pivot Table
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
VBA Codes of Pivot Table
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Codes of Pivot Table
Here is a macro:
Code: Select all
Sub CreateList()
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim s As Long
Dim m As Long
Dim a()
Dim n As Long
Application.ScreenUpdating = False
Set wshS = Worksheets("Sheet1")
Set wshT = Worksheets.Add(After:=wshS)
wshT.Range("B5").Resize(1, 7).Value = Array("Vendor", "Name", "GSTIN", "GSTR1", "Period", "GSTR3B", "Period")
m = wshS.Range("B" & Rows.Count).End(xlUp).Row
For s = 5 To m
If wshS.Range("B" & s).Value <> wshS.Range("B" & s - 1).Value Then
n = n + 1
ReDim Preserve a(1 To 7, 1 To n)
a(1, n) = wshS.Range("B" & s).Value
a(2, n) = wshS.Range("C" & s).Value
a(3, n) = wshS.Range("D" & s).Value
If wshS.Range("E" & s).Value = "GSTR1" Then
a(4, n) = wshS.Range("F" & s).Value
a(5, n) = wshS.Range("G" & s).Value
Else
a(6, n) = wshS.Range("F" & s).Value
a(7, n) = wshS.Range("G" & s).Value
End If
Else
If wshS.Range("E" & s).Value = "GSTR1" Then
If wshS.Range("F" & s).Value > a(4, n) Then
a(4, n) = wshS.Range("F" & s).Value
a(5, n) = wshS.Range("G" & s).Value
End If
Else
If wshS.Range("F" & s).Value > a(6, n) Then
a(6, n) = wshS.Range("F" & s).Value
a(7, n) = wshS.Range("G" & s).Value
End If
End If
End If
Next s
wshT.Range("B6").Resize(n, 7).Value = Application.Transpose(a)
wshT.Range("B6").Resize(1, 7).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: VBA Codes of Pivot Table
Thanks Hans Sir.
Perfect solution provided.I salute to you sir always.
Perfect solution provided.I salute to you sir always.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: VBA Codes of Pivot Table
but Hans date is appearing in GSTR1 as 02-09-2021 ( mmddyy )and it should be as 09-02-2021 ( dd/mm/yy).
However in GSTR3B is ok.
However in GSTR3B is ok.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Codes of Pivot Table
It works OK on my computer. Working around this will take some time.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA Codes of Pivot Table
That was less difficult than I thought. Does this work better?
Code: Select all
Sub CreateList()
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim s As Long
Dim m As Long
Dim t As Long
Application.ScreenUpdating = False
Set wshS = Worksheets("Sheet1")
Set wshT = Worksheets.Add(After:=wshS)
wshT.Range("B5").Resize(1, 7).Value = Array("Vendor", "Name", "GSTIN", "GSTR1", "Period", "GSTR3B", "Period")
m = wshS.Range("B" & Rows.Count).End(xlUp).Row
t = 5
For s = 5 To m
If wshS.Range("B" & s).Value <> wshS.Range("B" & s - 1).Value Then
t = t + 1
wshT.Range("B" & t).Value = wshS.Range("B" & s).Value
wshT.Range("C" & t) = wshS.Range("C" & s).Value
wshT.Range("D" & t) = wshS.Range("D" & s).Value
If wshS.Range("E" & s).Value = "GSTR1" Then
wshT.Range("E" & t) = wshS.Range("F" & s).Value
wshT.Range("F" & t) = wshS.Range("G" & s).Value
Else
wshT.Range("G" & t) = wshS.Range("F" & s).Value
wshT.Range("H" & t) = wshS.Range("G" & s).Value
End If
Else
If wshS.Range("E" & s).Value = "GSTR1" Then
If wshS.Range("F" & s).Value > wshT.Range("E" & t).Value Then
wshT.Range("E" & t) = wshS.Range("F" & s).Value
wshT.Range("F" & t) = wshS.Range("G" & s).Value
End If
Else
If wshS.Range("F" & s).Value > wshT.Range("G" & t).Value Then
wshT.Range("G" & t) = wshS.Range("F" & s).Value
wshT.Range("H" & t) = wshS.Range("G" & s).Value
End If
End If
End If
Next s
wshT.Range("B5").Resize(1, 7).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: VBA Codes of Pivot Table
Yes,now it is perfect.
Codes are working fine.
Great thank you for your efforts.
Codes are working fine.
Great thank you for your efforts.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA