· 9 months ago
Hello! Can anyone help me...
I found this code to turn rows of data with a unique ID into a single row per ID with multiple columns (min column 5 - including ID, maximum columns 25 - including ID). Then it deletes all of the duplicate rows based on unique ID. This code sort of works, the only problem is it isn't transferring two columns of data.
The number of rows of data per unique ID varies from 1 to 6 (therefore I would need minimum 5 columns to maximum 25 columns)
There are 20,000 rows of data but I can break the data up by department for a minimum of 5,000 rows of data and run each department separately.
Thanks for your help!
My data looks something like this
A B C D E
ID DESCRIPTION STATE # DATE
3 CPR US 567 6/19/2019
3 AET US 568 6/19/2019
4 CPR US 6/19/2019
4 AET
4 AED
etc.
I want it to look like this
A B C D E F G H I J K L M ETC.....
ID DESCRIPTION STATE # DATE DESCRIPTION STATE # DATE DESCRIPTION STATE # DATE
3 CPR US 567 6/19/2019 AET US 568 6/19/2019
4 CPR US AET US AED US
Here is the code I found that sort of works (probably for what it was written for, it carries over only the data in columns D and E and omits column B & C... leaving two blank columns per data set. I like that it deletes the duplicates after moving all of the data to a single column based on unique ID
Sub Addresses_To_Columns()
Dim lastRow As Long
Dim addressCount As Integer: addressCount = 0
lastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = lastRow To 3 Step -1
If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
addressCount = addressCount + 1
Range(Cells(i - 1, 10), Cells(i - 1, (addressCount * 5) + 10)) = Range(Cells(i, 4), Cells(i, (addressCount * 5) + 4)).Value
Rows(i).Delete
Else
addressCount = 0
End If
Next i
End Sub
I found this code to turn rows of data with a unique ID into a single row per ID with multiple columns (min column 5 - including ID, maximum columns 25 - including ID). Then it deletes all of the duplicate rows based on unique ID. This code sort of works, the only problem is it isn't transferring two columns of data.
The number of rows of data per unique ID varies from 1 to 6 (therefore I would need minimum 5 columns to maximum 25 columns)
There are 20,000 rows of data but I can break the data up by department for a minimum of 5,000 rows of data and run each department separately.
Thanks for your help!
My data looks something like this
A B C D E
ID DESCRIPTION STATE # DATE
3 CPR US 567 6/19/2019
3 AET US 568 6/19/2019
4 CPR US 6/19/2019
4 AET
4 AED
etc.
I want it to look like this
A B C D E F G H I J K L M ETC.....
ID DESCRIPTION STATE # DATE DESCRIPTION STATE # DATE DESCRIPTION STATE # DATE
3 CPR US 567 6/19/2019 AET US 568 6/19/2019
4 CPR US AET US AED US
Here is the code I found that sort of works (probably for what it was written for, it carries over only the data in columns D and E and omits column B & C... leaving two blank columns per data set. I like that it deletes the duplicates after moving all of the data to a single column based on unique ID
Sub Addresses_To_Columns()
Dim lastRow As Long
Dim addressCount As Integer: addressCount = 0
lastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = lastRow To 3 Step -1
If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
addressCount = addressCount + 1
Range(Cells(i - 1, 10), Cells(i - 1, (addressCount * 5) + 10)) = Range(Cells(i, 4), Cells(i, (addressCount * 5) + 4)).Value
Rows(i).Delete
Else
addressCount = 0
End If
Next i
End Sub