Posts by mythu

sure give me moment please

I would like to merging data that contains repeating ID's, specifically in cases where a single ID appears multiple times with unique DOS (Date of Service) entries. My objective is to consolidate the notes associated with each unique DOS entry into a single cell, similar to the example I provided earlier.

So all notes and thier respective dates will be merged for each unique ID

Thank you its working fine but there is one problem that is it should concatenate the Dates and Notes for unique ID. Right now code is concatenating based on ID's and Dates:

For Example for Id# 14 it should concatenate like this in ascending order

One more favor is it possible to convert the result into below format with dates and brackets

Thank you very much Mumps

Using ScreenUpdating and Calculation can save time, but they may not be effective in all situations.

The amount of data being processed varies from week to week, but it will never be less than 50,000 rows.

Hello, Yes you are right and one more thing is this code takes alot of time on large dataset for processing if you can make its processing more faster i would appreciate.

I have data in Sheet1 with 4 columns: ID, Sequence, DOS, and Note.

My code creates a duplicate sheet and removes columns B and D, keeping only columns A and C. Then, it removes any duplicate entries based on the values in columns A and C.

Next, the code looks up data from Sheet1 using columns A and C as the key, and concatenates the values in column D. It then pastes this data into column C of the duplicate sheet.

I would like to add a few more lines of code to include column 2, Sequence, so that the data is copied based on ascending order of sequence values. However, I'm having difficulty implementing this and would appreciate any assistance.

Sub ConvertRawData()
    Dim result      As String
    Dim lastRow     As Long
    Dim lastRow2    As Long
    Dim i           As Long
    Dim q           As Long
    Dim ws2         As Worksheet
    
    'Duplicate Sheet1 and move it to after Sheet2
    N = Sheets.Count
    Sheets("Sheet1").Copy After:=Sheets(N)
    
    'Remove columns B and D from the duplicated sheet
    ActiveSheet.Range("B:B,D:D").Delete Shift:=xlToLeft
    
    'Remove duplicates from columns A and B in the duplicated sheet
    With ActiveSheet.Range("$A$1:$B$50")
        .RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    End With
    
    Set ws2 = ActiveSheet
    lastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    
    For q = 2 To lastRow2
        For i = 2 To lastRow
            
            If Sheets("Sheet1").Cells(i, "A") = ws2.Cells(q, "A").Value And _
               Sheets("Sheet1").Cells(i, "C") = ws2.Cells(q, "B").Value Then
            If result <> "" Then
                result = result & " "
            End If
            result = result & Sheets("Sheet1").Cells(i, "D").Value
        End If
    Next i
    ws2.Cells(q, "C").Value = result
    result = ""
Next q
End Sub

Can someone please help me on this. I would appreciate your help.

Alright Thank you very much.

Sir Thank you very much but i am looking for the same solution in VBA. I would appreciate if you can do something.

There are multiple Workbooks in a folder and have similar column which Header name is "Name" but in each file column position is chnaged.

I want to search that header in 1st row of each workbook if finds then copy that entire column from multiple workbooks availble in Folder and Paste Unique result (values) into an open workbook where from code is being run.

There is one more thing that i want to extract multiple column by Header please add Array method so i can add more column name. I have attached 3 workbooks and result file

I would appreciate your help.

Sub MultipleSimilarColinto_1()
 
    Dim xFd         As FileDialog
    Dim xFdItem     As String
    Dim xFileName   As String
    Dim wbk         As Workbook
    Dim sht         As Worksheet
    Dim twb         As Workbook
    Dim LastRow As Long
    Dim ws As Worksheet
    Dim desWS As Worksheet
    Dim colArr As Variant
    Dim order As Long
    Dim i As Long

    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ActiveWindow.View = xlNormalView
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    Set twb = ActiveWorkbook
    Set desWS = twb.Sheets("Sheet1")
    If xFd.Show Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
    Else
        Beep
        Exit Sub
    End If
    xFileName = Dir(xFdItem & "*.xlsx")
    Do While xFileName <> ""
        
        Set wbk = Workbooks.Open(xFdItem & xFileName)
colArr = Array("Name")
        
        For Each ws In wbk.Sheets
            
            If ws.Name <> "Sheet1" Then
            
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            
            For i = LBound(colArr) To UBound(colArr)
                    order = ws.Rows(1).Find("Name", LookIn:=xlValues, lookat:=xlWhole).Column
                    ws.Range(ws.Cells(2, order), ws.Cells(LastRow, order)).Copy desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Next i
        End If
            
        Next ws
        wbk.Close SaveChanges:=True
        xFileName = Dir
    Loop
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Thank you very much for the assistance gijsmo

gijsmo I like the way you wrote this code and formula but both are extremly slow even then mine above code. I have more than 350K rows wit the a lot data. Where every function goes slow.

I appreciate your help.

KjBox Is there any solution that you can provide or modify the above mine code to calculate accurate result. I do not want to remove the 0 from start of the string i need to keep these zero.

The problem is simple COUNTIF answers are wrong with the above attached data, which forced me to move with the code.

Yes you're right COUNTIF is more faster than VBA but that time COUNTIF is not giving an accurate answer. You can try on attached file.

First i used COUNTIF then SUMPRODUCT since countif answers were wrong then went to sumproduct which was perfect but its processing were slow over large datasets.

Then found above code. I would appreciate if you could help to modify the above code.

I am using CountIF VBA version which gives me ease due to its fast calculation while working with the large datasets. My Data has text values as well as numerice values so it is basicaly a combination of different values.

I just want to count the number of repeated values but my function is giving wrong result as you can see in attached file.

I would appreciate if someoene can look into it and make it more efficent in terms of:

Accurate count for repeated values

Code Speed

and the last thing is i want to use this code in Personal.XLSB so i can recall it using shortkey.

I hope to get help. Thanks

I did it thank you very much.

I understand but if it can be done even later. I would apreciate it. Otherwise no problem and thank you for being so nice

Thank you. Its opening the Calendar now but not writing the Dates. I would appreciate if you could resolve this too.