Results 1 to 4 of 4

Thread: How to consolidate data in a single worksheet?

  1. #1
    Join Date
    Nov 2010
    Posts
    2

    How to consolidate data in a single worksheet?

    I have an excel worksheet with 20,000 dissimilar book isbn, one isbn for each cell. Out of the20,000 here are about 5,000 or10,000 duplicates with varies qtys. Is there a method to merge the duplicate isbn and qty to a particular cell without having to do it physically. In column A I have all the isbn and Column B the qty.

  2. #2
    Join Date
    Apr 2008
    Posts
    4,642

    Re: How to consolidate data in a single worksheet?

    According to me one easy method is to create a Filtered List of single values someplace else in your workbook and then use SUMIF.
    1. Choose Column A
    2. Data...Filter...Advanced Filter
    3. Copy to a different position (e.g. G1) sole value
    4. This should generate a record containing a particular instance of each ISBN.
    5. In K2 enter =SUMIF (A: A, G2, B: B) and drag it down:

    This will calculate the cells in Column B which match up to the value in the criteria dispute when it's establish in Column A.

  3. #3
    Join Date
    May 2008
    Posts
    4,570

    Re: How to consolidate data in a single worksheet?

    To consolidate data by position, follow these steps:
    1. Type the data on Sheet1 and sheet 2
    2. Click the upper-left cell of the target area for the consolidated records.
    3. Then On the Data menu, select Consolidate.
    4. In the occupation list, select the outline function that you desire Microsoft Excel to use to consolidate the data. In this example, use Sum.
    5. In the position box, type every source area you desire to consolidate, and then click Add.
    6. Repeat step for the entire source area that you wish to consolidate.
    7. Click OK.

  4. #4
    Join Date
    May 2008
    Posts
    4,345

    Re: How to consolidate data in a single worksheet?

    To consolidate data by category, Type the data on Sheet then follow these steps:
    1. Click the upper-left cell of the target area for the consolidated data,
    2. Then On the Data menu, click Consolidate.
    3. In the purpose list, choose the outline function that you desire Microsoft Excel to use to consolidate the data. In this case, use Sum.
    4. In the location box, type every foundation area you want to consolidate and then tick Add.
    5. Repeat the step for all cause areas you desire to consolidate.
    6. Under Use labels in, tick to choose the Left column check box
    7. Click OK.

Similar Threads

  1. Can I print more than one Excel worksheet on a single page
    By Anchal in forum MS Office Support
    Replies: 2
    Last Post: 27-01-2012, 07:09 PM
  2. Replies: 3
    Last Post: 11-01-2012, 11:13 AM
  3. Replies: 8
    Last Post: 04-12-2011, 11:24 AM
  4. Filter row in a single worksheet
    By Karunashankar in forum Windows Software
    Replies: 10
    Last Post: 28-07-2011, 12:13 PM
  5. Import data from one excel worksheet to another
    By arjun rantu in forum Windows Software
    Replies: 4
    Last Post: 28-11-2010, 12:28 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,655,723.48102 seconds with 17 queries