Ask questions via twitter! Message any question to @answers on twitter. We'll publish the question and send you a reply each time there's a new answer.
Next Question

Answered Question

willmatic willmatic
 

Mahalo is adding a tip to all questions that don't offer a tip.
 M¢25  Funded By Mahalo ? |  August 12, 2009 08:33 PM

How can i consolidate worksheets using Excel?

I have several worksheets each with columns containing different types of data. however each worksheet contains a column of "account ID" which has similar values across each worksheet. what i want is a single worksheet, that contains all of the columns that each separate worksheet has, and a first column of "account ID." Every worksheet does not contain every unique account ID, so i cannot simply sort all of the worksheets by "account ID" then copy and paste the relevant columns.
Interesting Question?  Yes (0)   No (0)   
RSS
 
 

Best Answer  Decided by Votes

aleghart aleghart
 
 August 13, 2009 02:52 AM
You'll have to edit the layout so each column is in its own position.

For instance, on Sheet1, col-01 and col-02 are find.
On Sheet2, move the columns over so they don't overlap Sheet1.
On Sheet3, move the columns over so they don't overlap Sheets1 & 2.
...so on, and so on.

Once each sheet is laid out physically so all columns are in good positions, you can create a final sheet for consolidation.

On this sheet (Sheet 4), copy all the column headings from each sheet.
Then, go through each sheet, select the cells with data, copy.
Switch to Sheet4 and paste into empty cell.

Everything should be aligned with the proper columns, and you won't have data under the wrong heading.

If you want the sample Excel file, just use the URL of the image and replace 'jpg' with 'xlsx'.

http://rsrvd.com/mahalopix/ma-excel-columns.jpg


Tags: formatting, excel, columns

Helpful Answer?  (0)   (0)    Tip aleghart for this answer
Permalink | Report
   Reply  
 
 
willmatic willmatic
 
August 13, 2009 02:23 PM
this manual approach would take forever with the amount of data i have. i would have to search through each worksheet to find the correct account IDs. sheet 1 has about 150 account IDs, but the other worksheets only have around 50. they are all account IDs that are in sheet 1, but the other sheets do not include all 150. this means my final worksheet will have many empty spots, which is ok... I simply cannot go through by hand and copy everything over.

Report
 
 
aleghart aleghart
 
August 13, 2009 08:40 PM
The only response I have to that is that the data sets shoud have been designed properly in the first place.

I see it all the time...no database experience at the beginning of the project, then get knee-deep and run into problems.

It a lot of tedious work to get everything formatted correctly. Unfortunately, those are options: massage it by hand, or start over with a solid design.

Report
 
 
willmatic willmatic
 
August 25, 2009 07:15 PM
Well if anyone stumbles across this down the road, i figured out how it can be accomplished. using the Vlookup function in Excel, you can create a function that can call information from the other sheets. i put the account_id column on the far left of each sheet, created a new sheet with account_id on the left, and each of the other columns present in the other sheets, and filled out the Vlookup formula for each column. worked great!

Report
 
 

Answer this Question

How tips and payments work

This question has already been resolved. You may add an answer to it but you will not be eligible to win best answer or any associated tips.

Related Questions

View All Software Questions

Ask a Question


140 characters left
Top of Page
Buy Mahalo Dollars with Credit Card or PayPal

Top Members

This Week All Time
  • buddawiggi
    buddawiggi
    2nd Degree Black Belt
    26935 Points
    M$783.09 Earned
  • kty2777
    kty2777
    Purple Belt with a Brown Tip
    5400 Points
    M$199.92 Earned
  • opher
    opher
    Purple Belt
    4132 Points
    M$185.92 Earned
   See All
 

Most Popular Tags

mahalo(1589)
twitter(944)
iphone(462)
music(459)
google(355)
food(314)
internet(303)
online(293)
beer(278)
money(262)
movies(255)
facebook(253)
apple(251)
aotd(235)
answers(221)
health(217)
video(205)
dog(204)
free(201)
windows(196)
   See All
 

Categories

Welcome New Members


 
 
Mahalo Dollars are the currency of Mahalo Answers.

Each Mahalo Dollar costs $1.

Once you earn more than 40 Mahalo Dollars, you can request to be paid via PayPal. Each Mahalo Dollar is currently worth $0.75 when paid out via PayPal. Learn More

 
 

Please log in to use this function.

Answers Home | Ask a Question | Earn Points | How Tips Work | Widget