Transpose data with same value to one row

[aznan79]

Hello, I have been cracking my head for two days but can’t seems to figure out a way to do this in Excel and I would like to see if anyone from the community could help.

I have a data (sheet name Data) and I would like to know if it’s possible to run a macro to convert it to the format as shown in (sheet name Macro-ed Result)

Each family has one unique Family No and I would like all information about a Family to be in one row so that it become like one record as shown in the Sheet name Macro-ed Result.

The macro should also remove duplicated email addresses automatically.

Attached is the excel spreadsheet and I would really appreciate it if you could help me on this.

Thanks in advance
Aznan Nagor
The International School of Penang (Uplands)

[stanleydgromjr]

aznan79,

Welcome to the Excel Forum.

Can worksheet Data, for Child Name, contain more than 4 unique children?

Can worksheet Data, for Email #, contain more than 4 unique emails?

[aznan79]

Hi, thanks for your reply , much appreciated.

Yes, there can be only maximum of 7 children and 7 unique email addresses.

Thanks in advance
Aznan Nagor

[stanleydgromjr]

aznan79,


Are all the Email #'s for a Family No group the same e-mail address (per your example raw data)?

Family No:
1961
group is made up of 6 rows

Child Name contains:
John
Anna

Email 1 contains 4 entries for:
[email protected]

So you want the Results column for Email 1 to display only one of the 4 duplicates?

Can there be different Email 1 addresses in this group?

If so, how do you want the results to be displayed in the Results worksheet?

[aznan79]

Hi Stan, no, the email addresses can be very different from one to another as it's Parents' personal email addresses. The raw data was just a sample.

Thanks in advance
Aznan

[aznan79]

Yes, i want it to only display one of the 4 duplicates.

Thanks in advance
Aznan Nagor

[stanleydgromjr]

aznan79,

the email addresses can be very different from one to another as it's Parents' personal email addresses

If Email 1 contains two different e-mail addresses, how do you want the Results worksheet to display the 2 entries in EMail 1?

[aznan79]

Hi Stan, if email 1 contains two different email addresses, can it be just transposed horizontally to the next cell of the same row ?

All i want is to have all records of a particular family into one row.

Thanks in advance
Aznan Nagor

[stanleydgromjr]

aznan79,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.

[aznan79]

Hi, i followed the steps and executed the Macro but i'm getting the error message as shown in the screenshot below:-

macro error.jpg

Please advise and thanks in advance
Aznan

[jindon]

if email 1 contains two different email addresses, can it be just transposed horizontally to the next cell of the same row ?

All i want is to have all records of a particular family into one row.

aznan79,

How are the emails separated ?

The following code assuming emails are separated with a comma.

Please Login or Register  to view this content.

[stanleydgromjr]

aznan79,

I fixed the error.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgDataV2 macro.

[aznan79]

Hi, thanks for all of your help, it's working!

Regards
Aznan

[stanleydgromjr]

aznan79,

You are very welcome. Glad I could help.

Come back anytime.