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

**MikeSTO** · 03-17-2021, 07:01 AM

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

**Glenn Kennedy** · 03-17-2021, 08:16 AM

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...

**MikeSTO** · 03-17-2021, 08:39 AM

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!

**Glenn Kennedy** · 03-17-2021, 08:40 AM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Creating a list based on criteria from another column

Creating unique list from a column with matching criteria from adjacent columns of table

Formula to create a list from One Column based on criteria from two other columns

[SOLVED] VBA Creating a list of unique values from one column based on criteria from another column

[SOLVED] LIST to TABS: Creating a copy of an existing tab for each name in a column

Creating a comma seperated list based on a search criteria from a column

Creating a list in one column based on criteria in two other columns!?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions