Solved

Excel 2010 - Flattening of Data in 1 Row

Posted on 2013-12-06
17
447 Views
Last Modified: 2013-12-13
Hi Attached please find the sample data.

Currently there are multiple rows for the Account #. RA Date and RD Date are the only 2 columns that are different for each row.

I want the data flattened in 1 row so that all data is in one row for each account number. How can I do this in Excel? I am a beginner Excel person.

Thanks
Test.xls
0
Comment
Question by:rustypoot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
17 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 39702442
Question
do you always have 4 rows for every Account ? or it may vary some 4 some 3 some 6 ... ?
then you want to endup with 1 row for each Account and all the columns as per your example then again will the columns vary or they are what you showed ?
gowflow
0
 

Author Comment

by:rustypoot
ID: 39702480
Some accounts may have more than 4 rows; in my example I used only 4 but I know some other Accounts have 6 or even more rows!
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39702492
so then how do we flatten ... you will not have the same header !!! as when you have:

for 3 you will have
RA Date      RD Date      RA Date1      RD Date1      RA Date2      RD Date2

for 6 you will have
RA Date      RD Date      RA Date1      RD Date1      RA Date2      RD Date2      RA Date3      RD Date3      RA Date4      RD Date4      RA Date5      RD Date5

etc ....
so data will not be aligned in column ???

May I ask what are you trying to achieve ? maybe we can understand better what you want ?
gowflow
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:gowflow
ID: 39703054
Question
Will there always be 4 rows for every Account ? or more or less ?
Presume you will have Accounts like this
BA1234
BA1234
BA1234
BA1234
BA1235
BA1235
BA1236
BA1236
BA1236
BA1236
etc...
and you want to end up with
BA1234
BA1235
BA1236
and all its related info in the columns right ?
gowflow
0
 
LVL 1

Expert Comment

by:jwlemmon
ID: 39703074
What about the complaint and the names, do they have to be the same or is it only the account?
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39707084
Did you had a chance to check out my comment ?
gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39710664
rustypoot,
Any news ?
gowflow
0
 

Author Comment

by:rustypoot
ID: 39714751
Sorry for the Delay! I was out of town! Thank you for the response though.

The end users want to see the data flattened in 1 Row. Only difference if for RADate and RDDate. I enquired more and they are ok with 6 dates as below in 1 Row along with other columns.

RA Date      RD Date      RA Date1      RD Date1      RA Date2      RD Date2      RA Date3      RD Date3      RA Date4      RD Date4      RA Date5      RD Date5

Can this be done now that there are fixed # of columsn requested? Thanks
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39714760
yes it can beside the dates you have the other items they come after the dates ??? give me the whole line.
gowlfow
0
 

Author Comment

by:rustypoot
ID: 39714779
Attached please find the sample file. Thank you.
Test.xls
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39714791
Fine let me recap if we have 3 it would go on the first 3 then the rest in the appropriate columns if 4 the first 4 etc... and this size would be fix right ?

Second question you want to flatten on a new sheet ? would be easier to read ?

Third can you post more data so the test would be better ?

gowflow
0
 

Author Comment

by:rustypoot
ID: 39714922
Sure I will post the updated file by tomorrow. Thanks
0
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 39715217
ok here it is.

1) Load the file and enable macros.
2) I have extended some Example in sheet1.
Rules: Data is in Sheet1  and header is in row1 no blank rows and data to follow under row1 as per the example.
3) When you press on the button Flatten Data it will display info that is self explanatory.

Try running the macro twice and see what it give you.

Pls let me know your comments.
gowflow
FlattenData.xls
0
 

Author Comment

by:rustypoot
ID: 39715483
Thank you I will test it out and definitely give you the feedback Thanks
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39715999
ok fine
gowflow
0
 

Author Comment

by:rustypoot
ID: 39717634
Thank you. It will work out for me. Thanks
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39717697
Great glad I could help
gowflow
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question