+ Reply to Thread
Results 1 to 6 of 6

Find the top 2 city of populations also data can increase in A, B C column

  1. 06-12-2018, 11:07 AM #1
    Merrysa
    Merrysa is offline
    Registered User
    Join Date
    06-12-2018
    Location
    india
    MS-Off Ver
    2013
    Posts
    93

    Find the top 2 city of populations also data can increase in A, B C column

    HI All,
    Please help me on the below. i am new user dont know why attachment option is not working to upload file

    Find the top 2 city of populations also data can increase in A, B C column


    Record Country City PopulationsCapture11111.PNG
    1 USA Norfolk 74000
    2 USA Norfolk 39000
    3 USA Norfolk 11525
    4 USA Norfolk 54125
    5 USA Newark 5000
    6 USA Newark 2589
    7 USA Newark 7400
    8 USA Newark 9526
    9 Canada Monterial 1582
    10 Canada Monterial 2154
    11 Canada Monterial 8742
    12 Canada Monterial 8758
    13 Canada Vancovour 1254
    14 Canada Vancovour 8598
    15 Canada Vancovour 4582
    16 Canada Vancovour 2547




    output is the below


    Country City Populations
    USA Norfolk 74000
    USA Norfolk 54125
    USA Newark 9526
    USA Newark 7400
    Canada Monterial 8742
    Canada Monterial 8758
    Canada Vancovour 8598
    Canada Vancovour 4582
    Reply With QuoteRegister To Reply
  2. 06-12-2018, 08:43 PM #2
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Find the top 2 city of populations also data can increase in A, B C column

    Hi Merrysa. Welcome to the forum.

    With source data in columns A:C this in E2:F9
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    and this in G2:G9
    Formula: copy to clipboard 
    Please Login or Register  to view this content.




    A
    B
    C
    D
    E
    F
    G
    1
    Country
    City
    Populations
    Country
    City
    Populations
    2
    USA
    Norfolk
    74000
    USA
    Norfolk
    74000
    3
    USA
    Norfolk
    39000
    USA
    Norfolk
    54125
    4
    USA
    Norfolk
    11525
    USA
    Newark
    9526
    5
    USA
    Norfolk
    54125
    USA
    Newark
    7400
    6
    USA
    Newark
    5000
    Canada
    Monterial
    8758
    7
    USA
    Newark
    2589
    Canada
    Monterial
    8742
    8
    USA
    Newark
    7400
    Canada
    Vancovour
    8598
    9
    USA
    Newark
    9526
    Canada
    Vancovour
    4582
    10
    Canada
    Monterial
    1582
    11
    Canada
    Monterial
    2154
    12
    Canada
    Monterial
    8742
    13
    Canada
    Monterial
    8758
    14
    Canada
    Vancovour
    1254
    15
    Canada
    Vancovour
    8598
    16
    Canada
    Vancovour
    4582
    17
    Canada
    Vancovour
    2547
    Dave
    Reply With QuoteRegister To Reply
  3. 06-12-2018, 08:56 PM #3
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find the top 2 city of populations also data can increase in A, B C column

    For the future

    Attach example EXCEL file (not a picture, pasted text or any other than Excel format!). This file should be attached to a new post. Never change your original post (except admin's request).

    In attached Excel file try not to use:
    • merged cells
    • password protection
    • autorun VBA code
    • unnecessary formatting like: colours, borders, aligning another than default, etc...
    • unnecessary zooming/grouping/freezeing
    You have words to logically describe the problem

    To attach an Excel file to your post,
    • desensitize data
    • remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post
    Reply With QuoteRegister To Reply
  4. 06-13-2018, 06:15 AM #4
    Merrysa
    Merrysa is offline
    Registered User
    Join Date
    06-12-2018
    Location
    india
    MS-Off Ver
    2013
    Posts
    93

    Re: Find the top 2 city of populations also data can increase in A, B C column

    Hi Dave,
    Thank you for your quick response, If i consider the attached example where i have to take top 4 populations city wise but 2nd city of each country will show only top 3 populations how can we do


    that mean : in USA country there are three city ( Norfolk, Newark and St Lui) in this case Norfolk will take 4, Newark 3 and St Lui 4
    Attached Files Attached Files
    Last edited by Merrysa; 06-13-2018 at 06:20 AM.
    Reply With QuoteRegister To Reply
  5. 06-14-2018, 02:00 AM #5
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Find the top 2 city of populations also data can increase in A, B C column

    I don't understand.

    There are 6, 8 and 6 each of Norfolk, Newark and St. Luis. That shouldn't stop returning the top 4 of each city. The same holds true for all other cities except Batsman, Chile. There is only one.

    All that can be returned for that city is 1953.

    What do you want to do?

    Perhaps hand type an addition to the upload like posted in Post #1 ... only in the workbook.

    Edit I went ahead and guessed. Please see the next post.
    Last edited by FlameRetired; 06-14-2018 at 02:37 AM.
    Reply With QuoteRegister To Reply
  6. 06-14-2018, 02:43 AM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Find the top 2 city of populations also data can increase in A, B C column

    The attached uses a helper column to resolve the uneven numbers of cities.

    The helper formula assigns an index number to each first occurring country/city.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    The formula to return country/cities entered in I2 filled down and across column J until you get blanks is.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    The formula to return top 4 populations each (Batsman only one.)
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Merge city names from 4 columns into one column with "/" between each city
    By alipezu in forum Excel General
    Replies: 8
    Last Post: 10-16-2017, 03:08 AM
  2. generate statistics from multiple populations/groups within large dataset?
    By ngeo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-22-2017, 08:37 AM
  3. [SOLVED] Looping through rows of data to find a certain city
    By bilich in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-10-2017, 12:27 PM
  4. [SOLVED] Find Date in Column A, Increase Values in Columns B, C, and D through Option Buttons
    By aappleto in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-25-2013, 09:17 AM
  5. [SOLVED] Find word in column, increase value
    By Thanticore in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-03-2012, 03:07 PM
  6. [SOLVED] Find nonempty cell above in column and increase number
    By davegscott in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 09-18-2012, 07:01 PM
  7. Find the time at which the difference in data points starts to increase
    By jblumGRT in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-24-2009, 02:00 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1