Padraig O'Dea
asked on
Simple Excel re-arrange data. (I can work in access too).
Hi,
This is breaking my heart and should be much easier!
I have 10,000 rows in a sheet.
Current FORMAT
==============
John Red
John Blue
John Grey
Paul Black
Jane Green
Jane Red
Required Format
==============
John Red Blue Grey
Paul Black
Jane Green Red
Any help out there? I have spend almost two hours on this simple matter!
EENYCI1001.xlsx
This is breaking my heart and should be much easier!
I have 10,000 rows in a sheet.
Current FORMAT
==============
John Red
John Blue
John Grey
Paul Black
Jane Green
Jane Red
Required Format
==============
John Red Blue Grey
Paul Black
Jane Green Red
Any help out there? I have spend almost two hours on this simple matter!
EENYCI1001.xlsx
Hi,
pls try
pls try
Sub macro1()
For Idx = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
Set c = Range("A" & Idx)
If c.Offset(-1) = c Then
Range(c.Offset(, 1), c.End(xlToRight)).Copy c.Offset(-1).End(xlToRight).Offset(, 1)
c.EntireRow.Delete
End If
Next
End Su
Regards
ASKER
Thanks folks,
Unfortunately my VBA i not great. Although, I am currently trying to tweak your solutions.
The data that I provided was a simplified very of the live data which is simple too.
I attach the actual live data.
The source data is in the first sheet.
The two simple requirements are shown in the second and third sheet. (Yellow & Orange colours may clarify).
Any chance that you could tweak your script to produce results similar to "Requirement 1" and "Requirement 2".
Thanks for you time and efforts.
Padraig_Contacts_10012017.xlsx
Unfortunately my VBA i not great. Although, I am currently trying to tweak your solutions.
The data that I provided was a simplified very of the live data which is simple too.
I attach the actual live data.
The source data is in the first sheet.
The two simple requirements are shown in the second and third sheet. (Yellow & Orange colours may clarify).
Any chance that you could tweak your script to produce results similar to "Requirement 1" and "Requirement 2".
Thanks for you time and efforts.
Padraig_Contacts_10012017.xlsx
See attached formula driven solution.
Sheet2 has solution:
1) two pivot tables with only one column each to get a list of unique values from each column.
2) row 1 incrementing number to cover the number of different options per value (name).
3) columns B to H formula to extract each option per value (name). This relies on all entries for each value being listed together; not necessarily sorted alphabetically. The pivot will then sort the values.
Hope that helps
Thanks
Rob H
EENYCI1001.xlsx
Sheet2 has solution:
1) two pivot tables with only one column each to get a list of unique values from each column.
2) row 1 incrementing number to cover the number of different options per value (name).
3) columns B to H formula to extract each option per value (name). This relies on all entries for each value being listed together; not necessarily sorted alphabetically. The pivot will then sort the values.
Hope that helps
Thanks
Rob H
EENYCI1001.xlsx
Did you look at attached file in 41954977?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In the Access environment, you can use the (DConcat) code in this article.
https://www.experts-exchange.com/articles/2380/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html
https://www.experts-exchange.com/articles/2380/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html
ASKER
Thanks to ROb,Shaun,Rgonzo1971, Aikimark.
This was a real eye-opener. All is now resolved.
I had assumed (dangerous word) that this task was going to be a simple 10 minutes of manipulation.
But I got stuck for several hours.
I will try the Access command "dconcat" later - the Patrick Matthews one.
(I think I came across this guy before - very helpful stuff).
Thanks again ,all.
This was a real eye-opener. All is now resolved.
I had assumed (dangerous word) that this task was going to be a simple 10 minutes of manipulation.
But I got stuck for several hours.
I will try the Access command "dconcat" later - the Patrick Matthews one.
(I think I came across this guy before - very helpful stuff).
Thanks again ,all.
https://www.extendoffice.com/documents/excel/3153-excel-concatenate-if-same-value.html
You can also use VBA
Open in new window
Combined.xlsx