ok, thank you
Posts by mahesht
-
-
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 :
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
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
-
-
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
Code
Display MoreSub 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.
QuoteSadly 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.
-
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
Code
Display MoreSub 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
QuoteIf 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