+ Reply to Thread
Results 1 to 4 of 4

Creating a new list based on criteria in existing column (and map new columns to it)

  1. #1
    Registered User
    Join Date
    03-17-2021
    Location
    Zurich
    MS-Off Ver
    Excel 365
    Posts
    2

    Creating a new list based on criteria in existing column (and map new columns to it)

    Hi,

    I am somewhat at a loss of ideas (or maybe just don't see a simple solution). See attachment for where I would like to get to.

    Starting situation:
    I have a living document with a list that is being continuously updated (e.g. of properties). The layout cannot be changed. To every property there is a set of different buyers mapped, including the timeframe by when they would like to buy the property.

    Where I would like to get:
    Now I would like to create a new list (in a new sheet) with all properties of certain property type (e.g. "Apartment"), then indicate how many buyers are interested in this property (depending on their buying timeframe) and then also add that list of buyers in the columns behind.

    Is there any way of doing this? Unfortunately we are very limited in changing the layout of the current table.

    Thank you!

    Michael
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Creating a new list based on criteria in existing column (and map new columns to it)

    Three formulae needed:

    Orange shading (copied down)... note the results columns are in a different order to the source:
    =IFERROR(INDEX('Input needed'!C:C,AGGREGATE(15,6,ROW('Input needed'!$B$7:$C$20)/('Input needed'!$C$7:$C$20=Sheet1!$B$2),ROWS(B$3:B3)))&"","")

    Salmon pink shading (copied across and dnown):
    =IF($B3="","",SUMPRODUCT(('Input needed'!$C$7:$C$20=Sheet1!$B3)*('Input needed'!$B$7:$B$20=Sheet1!$C3)*("Buyers "&'Input needed'!$E$4:$N$4=Sheet1!D$2)*('Input needed'!$E$7:$N$20="x")))

    Light Green shading (copied across and dnown):
    =IFERROR(INDEX('Input needed'!$6:$6,AGGREGATE(15,6,COLUMN('Input needed'!$E$6:$N$6)/(INDEX('Input needed'!$E$7:$N$20,MATCH($C3,'Input needed'!$B$7:$B$20,0),)="x"),COLUMNS($F$3:F3))),"")

    If you want a DD box (as in B2...) and don't know th ebest way of making one, just ask...
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    03-17-2021
    Location
    Zurich
    MS-Off Ver
    Excel 365
    Posts
    2

    Re: Creating a new list based on criteria in existing column (and map new columns to it)

    You're a star, thanks so much for this perfect solution! I will try and incorporate into my "real" data set tonight and would ask in case of any problems. Again, thank you so much!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Creating a new list based on criteria in existing column (and map new columns to it)

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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. Creating a list based on criteria from another column
    By heweaver in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2019, 10:46 AM
  2. Replies: 6
    Last Post: 09-09-2018, 07:43 AM
  3. Replies: 2
    Last Post: 05-27-2018, 09:43 AM
  4. [SOLVED] VBA Creating a list of unique values from one column based on criteria from another column
    By bilbo85 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-09-2016, 02:38 PM
  5. [SOLVED] LIST to TABS: Creating a copy of an existing tab for each name in a column
    By Geoff. in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-10-2014, 06:07 PM
  6. Creating a comma seperated list based on a search criteria from a column
    By HUGH JORGAN in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2013, 05:20 PM
  7. Creating a list in one column based on criteria in two other columns!?
    By chelseasikoebs in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-02-2009, 11:00 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1