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

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

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

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

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

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