Posts by mahesht

ok, thank you

Thanks Roy, if you see there is already an module code available there, which works fine for column D, but when i use the same code for column K, it does not work.

Thanks Carim for the link, will check that out.

if you dont mind just one thing i wanted from the attached file in post#1, i can filter the unique date in column "Future Date" to separate sheet, that would be great

thanks Carim for your response

Could you clarify your two remarks :

Quote from Carim

1. Which VBA skills would you like to develop ? ( they are several methods to achieve the same final output )

i knowledge in vba is very poor, i am not sure on the multiple different ways of achieving this. Wondering, If this can be done through filter option

Quote from Carim

2. What do you mean by " the Structure in Result will not come in one go " ?

Whenever i do pivot i will have to drag the field in either row or columns to get the desired result.

If i have to get the output same sheet "Result", i feel this can be achieved only by vba code. My only issue is i have poor knowledge in understanding vba code.

Actually, i wanted to do this through VBA and not use the group feature.

Firstly, i wanted to enhance my vba skills and secondly, the sturcture in Result will not come in one go.

i need to each time have to do the pivot.

Hope you can help me to do this through vba

Thank you Carim for the response. I have checked the link, Yes, it will help me to group the data by date/month/years.

Thanks Roy for the attached excel. I had done similiar pivot already in my excel.

I am looking to do the same by vba code. Is this possible to do this.

HI! all

I have data in multiple columns, one of the column has date details. I want something similar to pivot table for the date column based on segregate by "Cus Sgm"

While i am able to do this by Pivot, but i would want to do further segregate by:

- Current Month (as individual date wise)

- Next Month ( as consolidated by months)

- Next Year (as consolidated by Year wise

is this possible to derive

i have attached excel sheet with sample in sheet "Result"

thanks

HI! all

In my excel i have below column heads

Field_AC

Source

What I am trying to do is count records based on Field_AC using "COUNTIFS" function

COUNTIFS(K1:K16,G2)

I also want to add one more criteria based on Source (Loc4) using the CountIfs function

COUNTIFS(P1:P16,H7)

attach image shows the result of the 1st criteria is 1 each for Field_AC and Loc4.

How can i use both these functions together.

Thanks

thanks rory

Thank RoyUk, well noted, will keep that in mind

Thanks Carim, yes it works now from your post #3 code

Thanks Carim, for your response, i put the code on the top line of the code and it gives me error "Variable Not defined"

HI!

I have an excel sheet which copies data from tab "X_Reports" to tab "Summary". When i am in Summary sheet and i trigger the below code, the result in Count column is giving me the correct values. However, when i am in different sheet, lets say "Daily Recon" it gives a result in Summary sheet in column Count is Zero

Kindly help me to modify the code to give a correct value even if i trigger from any other sheet.

below is the code

Sub FieldAC_Test2()
 On Error Resume Next
 Dim nc1 As New Collection, nc2 As New Collection, nc3 As New Collection, nc4 As New Collection
 Dim r As Range, cel As Range
 Dim wsMain As Worksheet, wsEMI As Worksheet
 Dim x As Integer
 Dim last As Long
 Set wsMain = Sheets("Summary")
 Set wsEMI = Sheets("X_Report")
 last = wsEMI.Cells(Rows.count, "D").End(xlUp).Row
 Set r = wsEMI.Range(wsEMI.Range("D12"), wsEMI.Range("E" & Rows.count).End(xlUp))
 For Each cel In r
 nc1.Add cel.Offset(, -1), cel
 nc2.Add cel, cel
 nc3.Add cel.Offset(, 1), cel
 nc4.Add cel.Offset(, 2), cel
 Next cel
 For x = 1 To nc1.count
 wsMain.Range("A" & Rows.count).End(xlUp).Offset(1).Value = nc1(x)
 wsMain.Range("B" & Rows.count).End(xlUp).Offset(1).Value = nc2(x)
 wsMain.Range("C" & Rows.count).End(xlUp).Offset(1).Value = nc3(x)
 wsMain.Range("D" & Rows.count).End(xlUp).Offset(1).Value = nc4(x)
 ' Instruction added to Count Unique Records'''''''''''''''''''''''''''''''''''''''''''''''''''''
 If nc1(x) <> "" Then wsMain.Range("F" & Rows.count).End(xlUp).Offset(1).Value = Evaluate("=COUNTIF(X_Report!D12:D" & last & ",A" & x + 1 & ")")
 Next x
 MsgBox "Done"
End Sub

Thanks Carim, and apologies for the confusion.

Quote

Sadly you are not answering the question .... but adding several new issues ...

yes, i have put the code provided in post# 21 and now it exactly tells me the count of each unique rows from the X_Report tab.

Thank you very much

HI! Carim

i have attached snap shot of the result it gives me in my original excel

Quote

It seems to me this question is not exactly the same as the one with which you started this very thread ...

Am I right or wrong ...???

Yes, Carim, the question pointed to my 1st thread. but it is indirectly related to this thread.

the code given in post# 19 is working and keeping the cell empty. But i wanted to move the count to column F. I tried to change the code from If nc1(x) <> "" Then wsMain.Range("E" to If nc1(x) <> "" Then wsMain.Range("F"

but it seems to copy extra count

the number of rows in X_Report is not always same. can this be change ($D$12:$D$68) to something D12:D

it copies the data from column D12 to column E and counts the number of rows from tab X_Report

below is the code. In my office file it gives me 1888, that is because the number of empty rows in quite high

in the same file it may be less.

wsMain.Range("E" & Rows.count).End(xlUp).Offset(1).Value = Evaluate("=COUNTIF(X_Report!$D$12:$D$68,A" & x + 1 & ")")

I am extremely sorry Sir since that original excel file is in my company folder and i cannot share for security reasons.

I am sharing the dummy excel which i had tried to replicate the information as close as possible.

The dummy file does not have this issue, probably the dummy data that i have created is not exactly replicate. But i have shown the result in the Summary tab where the empty rows are counted and shown as 1888

Sub FieldAC_Test()
 On Error Resume Next
 Dim nc1 As New Collection, nc2 As New Collection, nc3 As New Collection, nc4 As New Collection
 Dim r As Range, cel As Range
 Dim wsMain As Worksheet, wsEMI As Worksheet
 Dim x As Integer
 Set wsMain = Sheets("Summary")
 Set wsEMI = Sheets("X_Report")
 Set r = wsEMI.Range(wsEMI.Range("D12"), wsEMI.Range("E" & Rows.count).End(xlUp))
 For Each cel In r
 nc1.Add cel.Offset(, -1), cel
 nc2.Add cel, cel
 nc3.Add cel.Offset(, 1), cel
 nc4.Add cel.Offset(, 2), cel
 Next cel
 For x = 1 To nc1.count
 wsMain.Range("A" & Rows.count).End(xlUp).Offset(1).Value = nc1(x)
 wsMain.Range("B" & Rows.count).End(xlUp).Offset(1).Value = nc2(x)
 wsMain.Range("C" & Rows.count).End(xlUp).Offset(1).Value = nc3(x)
 wsMain.Range("D" & Rows.count).End(xlUp).Offset(1).Value = nc4(x)
 ' Instruction added to Count Unique Records'''''''''''''''''''''''''''''''''''''''''''''''''''''
 wsMain.Range("E" & Rows.count).End(xlUp).Offset(1).Value = Evaluate("=COUNTIF(X_Report!$D$12:$D$68,A" & x + 1 & ")")
 Next x
 MsgBox "Done"
End Sub

HI Carim

to answer to post# 9

Quote

If I understand your remark ... there is a difference in Excel between Empty and Blank ...

Are you copying the data in Column A from another source ...???

yes, i am copying the data from another sheet from the same excel.

i have tried as you suggested in post# 11 and am getting the below

#VALUE!

^{HI Carim, Actually my problem is solved, but i am not sure how to present my issue.}

^{I can see there is no data in column A. but it still counts the data from adjacent column B.}

If i keep my cursor on that particular field (where there is no data) and hit the delete key, it works post that

hope you understood my problem