+ Reply to Thread
Results 1 to 5 of 5

Combining similar areas of data

  1. #1
    Registered User
    Join Date
    02-12-2021
    Location
    san jose, ca
    MS-Off Ver
    365
    Posts
    6

    Combining similar areas of data

    I posed this question recently, but wasn't able to get an answer. I don't think I described the issue very well at all, so I thought I'd try again as I think I have a simpler way to explain.

    I have worksheets with data for two Olympic games (Rio in 2016 and London in 2012). Each worksheet has the events as columns and the participating countries as rows. As both the events and the participating countries vary from one Olympics to the next the column and row headings will be similar but not identical.

    The cells in each workbook contains the number of medals won by each country in each event.

    The ask is to merge the data into a 3rd worksheet showing the medal count for each country/event at both Olympics. For example Canada won 1 medal for swimming in London and 3 medals in Rio. The 3rd worksheet should have a medal count of 4 for Canada/Swimming
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Combining similar areas of data

    In A3 and copy down.
    Please Login or Register  to view this content.
    In B2 then copied across
    Please Login or Register  to view this content.
    In B3 then copied across and down
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Combining similar areas of data

    PQ is also very convenient


    Provide another solution, the fastest and best way is to not write any formulas, directly use the keyboard to enter the relevant requirements and then use the
    MOUSE operation to complete


    1. First create a red square frame for the picture (such as 1.JPG with file model)

    Menu \ Data \ Consolidate
    Pls Click the left mouse button to select the ranges area G1:K6

    2. Follow the red square frame for the picture (such as 2.JPG with file model) the end Press OK

    I have already prepared an animation teaching file because the forum limit GIF cannot exceed 100K, and the recording file is 657K, so it cannot be uploaded.
    Attached Images Attached Images
    Last edited by wk9128; 06-15-2021 at 10:38 AM.

  4. #4
    Registered User
    Join Date
    02-12-2021
    Location
    san jose, ca
    MS-Off Ver
    365
    Posts
    6

    Re: Combining similar areas of data

    Many thanks - this is perfect. Amazing that what seems a complex task can be handled so simply!

  5. #5
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Combining similar areas of data

    You're Welcome. Thank You for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Combining data from two similar tables into one PivotChart
    By tomolias in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-27-2020, 06:39 AM
  2. help combining similar macros
    By ocannon1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-09-2017, 09:49 AM
  3. [SOLVED] Combining two Worksheet_Change functions to clear contents in two areas same sheet
    By Plrod in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2016, 06:03 AM
  4. Combining similar names
    By kreativsoul in forum Excel General
    Replies: 3
    Last Post: 07-20-2012, 03:15 AM
  5. Combining rows with similar data, not exact. Help!
    By Tony2009 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-25-2012, 03:14 PM
  6. Combining rows with similar data, not exact
    By Tony2009 in forum Excel General
    Replies: 4
    Last Post: 05-24-2012, 12:09 PM
  7. Combining IF, AREAS and a variable?
    By Jenkkipurkka in forum Excel General
    Replies: 2
    Last Post: 12-17-2009, 10:22 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1