Posts by cristu

Thank you sir.

Hi KjBox

Sorry for delayed response

On the result sheet I want to replace any data not to add, It's one time run.

Thanks

Hello,

I have a large worksheet that contains detailed data on various column fields, an I only want to extract rows from a specific range strings and concatenate date to another sheet, separated by commas.

Strings: Person and DECDENT (extract text between)

I attach an example with 'extract.xlsx' sheet4 and the 'result' sheet as I want to look

Is there a way to do this via VB script/macro ?

Thanks

I found your solution at Concatenate & removing non-unique names

But I want to adapt it to copy columns from A to G

Option Explicit
Sub foo()
Dim i As Long, lr As Long, lr2 As Long
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
lr = s1.Range("A" & Rows.Count).End(xlUp).Row
With s1
.Range("A1:B2").Copy s2.Range("A1:G2")
For i = 3 To lr
lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
If .Range("A" & i) = .Range("A" & i - 1) Then
s2.Range("B" & lr2) = s2.Range("B" & lr2) & ", " & .Range("B" & i)
Else
.Range("A" & i & ":B" & i).Copy s2.Range("A" & lr2 + 1)
End If
Next i
End With
End Sub

Thank You

Thanks alansidman

But I have very large amount of data and pivoit tables doesn't help me so much. I have up to 50 columns and 50K rows and it's slow checking fields. Maybe some macro could be faster.

Hello,

I have a large number of rows in a workbook that contains detailed data on various fields.

A field has a unique id and I want to group the duplicate details into a single field, details separated by commas.

I attach an example with 'dgsec' sheet and the 'result' sheet as I want to look

Is there a way to do this via VB script/macro ?

Thank you

Quote from dangelor

Try this...

Code

Sheets.Add(after:=Sheets(Sheets.Count)).Name = Left(e, 31)

I got error: Runtime error '9': Subscript out of range

Debug:

.Parent.Cells.Copy Sheets(e).Cells(1)

It's starting to rename within 31 chars until above error.

Sorry I should be open a new thread, my bad. Apologize

Hello,

Based on the old thread:

Macro copy rows to sheets

The code it's working fine but I have problems when the sheet names exceeds 31 characters

How can I modify the code to truncate the name below 31 chars even it's longer ?

I got the following message: You typed an invalid name for sheet (pic attached)

Code:

Option Explicit


Sub test()
Dim e
Application.ScreenUpdating = False
With Sheets("cardio").Range("a6").CurrentRegion
For Each e In Filter(.Parent.Evaluate("transpose(if(countif(offset(" & _
.Columns(3).Offset(1).Address & ",0,0,row(1:" & .Rows.Count & "))," & _
.Columns(3).Offset(1).Address & ")=1," & .Columns(3).Offset(1).Address & _
",char(2)))"), Chr(2), False)
If Not IsSheetExists(e) Then
Sheets.Add(after:=Sheets(Sheets.Count)).Name = e
End If
.Parent.Cells.Copy Sheets(e).Cells(1)
With Sheets(e)
With .Range("a6").CurrentRegion
.AutoFilter 3, "<>" & e
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End With
Next
End With
Application.ScreenUpdating = True
End Sub


Function IsSheetExists(ByVal txt As String) As Boolean
On Error Resume Next
IsSheetExists = Len(Sheets(txt).Name)
On Error Goto 0
End Function

Thanks

Cristi

Re: Remove duplicate rows and concatenate data to single field

Got It!
Cheers

Re: Remove duplicate rows and concatenate data to single field

Thank You
It works!

What should I change in script if the unique filter is on column L and data to be appended in column Q. Right now is columns J and M
Thanks again
Cristi

Hello,

I have a .xlsx file with multple duplicate rows and in one column the result is different
I have made an example excel workbook to show you what I need.
The unique filter is by column J (idd)
In Sheet1 is the original data and Sheet2 how It should look like after removing duplicates and concatenate the data from column M (code) separated by commas

Thank You,
Cristi

Re: Multiple rows to columns

Thank You very much Mick
It's working.
Again, thanks

Hello,

I have a .csv contacts file with multiple rows for same contact and I want to put all details in a single line.
I have made an example excel workbook to show you what I need.
In Sheet1 is the phonebook contacts and Sheet2 how It should look like after some processing
Or at least to be all in one line delimited by comma or TAB where First Name: appear

Thank You,
forum.ozgrid.com/index.php?attachment/66988/

Re: Number lines on same data columns

Quote from snb;718495

Code

Sub M_snb()
    sn = Cells(1).CurrentRegion


    For j = 2 To UBound(sn)
      If sn(j, 2) <> sn(j - 1, 2) Then
        y = y + 1
        sn(j, 1) = y
      End If
    Next
    
    Cells(1).CurrentRegion= sn
End Sub

Display More

What should I modify in this script if the repeated data is in column C ?
Also If I move whole data starting with 4th line.
Thanks

Re: Number lines on same data columns

Thank You All.
I have managed my issue.

Re: Number lines on same data columns

Hi
I do not want to fill empty cells in A collumn.
Column A is empty and I want to numerotate in column A rows only when the data changes in B.

See attach
Thanks

Hello,

I want to add current numbers (numerotate) lines with the same data present in multiple columns.
I add an attach to understand better
Thank You

Re: Macro copy rows to sheets

jindon , you're the best !
That's what I want.

Thank You.

Hello,
I'm new around here and I want you help regarding an excel macro.
I have a sheet with data like this:
Column A Column B Column C etc
name address xxx
name address xxx
name address yyy
name address zzz
etc

I want to copy entire rows who contains xxx, yyy, zzz (Column C) to another sheets
xxx to sheet called xxx
yyy to sheet called yyy etc.

Ex. sheet called xxx which contains only xxx rows
Column A Column B Column C etc
name address xxx
name address xxx

Thank You