Pulling Items from a multiple column Range

**Grimace** · 05-04-2009, 08:21 PM

Good morning,
I have a sheet with Races as per the attached example.
I need to be able to pull the information into another section of the workbook and place it into order (ie Black 1,2,3,4) and then drop down to the next row and place Blue 1,2,3,4 etc. I am using a drop down menu to select the Course, and want the times to then populate to the right, each in its own cell.
Have tried the loop code available on a few different topics here however has not quite worked.
Hoping someone can assist

Thanks
Darren.

**rylo** · 05-04-2009, 09:22 PM

Hi

1) Where do you want the the output to be placed?

2) How do you want to start the macro? Button?

rylo

**Grimace** · 05-04-2009, 09:35 PM

Hi Rylo

I will be placing the data into cells running horizontally in another part of the same worksheet. My aim is to select a course from a drop down list (F3), and the relevant times will populate to the right of the course colour (E3..N3).
I can do the drop down and filtering etc, just have not quite got the code correct for searching the list and pulling the times.

And yes happy to have either a button on the sheet, or go through the menu to run the macro.

I have updated the example sheet with more info to give you a better idea of what I am after, including the unique course filter and the drop down menu under Time by Course.

Darren

**rylo** · 05-04-2009, 10:04 PM

Hi

If I understand your situation, you can do most of it with formulas.

G3:

=INDEX($D:$D,SUMPRODUCT(--($B$3:$B$24=$F3),--($C$3:$C$24=COLUMN()-6),ROW($B$3:$B$24)))

Copy down/across to cover your required area.

HTH

rylo

**JBeaucaire** · 05-04-2009, 10:07 PM

Perhaps like this:

EDIT: Bad first layout...corrected.

**Grimace** · 05-04-2009, 10:36 PM

Thank you so much to both of you. Both examples worked a treat.

Went with yours JBeaucaire simply because it removed data from blanks cells rather than repeating the last choice.

Fantastic !!!! 2 days of fumbling around was fixed by about 10 minutes of asking those who know !!!

Darren

**Grimace** · 05-05-2009, 12:44 AM

Hi All

One quick question .. Does the range you are drawing the info from have to be constant ?
I have just transferred the formula suuiplied into my working sheet, and it is returning a #VALUE! error if I amend the length of the column to take into account the maximum possible rows, where often some of those rows will be blank (the number of courses and races varies day to day)

Am I best to do a count to set the number of rows within the formula?

Darren

**JBeaucaire** · 05-05-2009, 12:53 AM

Any particular reason we didn't work with the "real" sheet in the first place?

The SUMPRODUCT formula has a LOT ranges, so make sure you extend the ROW ranges equally in all the formula segments.

The COLUMN()-6 in G3 is designed to get a value of "1", Column G is column 7, so 7 - 6 = 1 and from them on the number increments by one as the formula is copied to the left. That doesn't need to change unless you CHANGE the column of the first race.

If the first race will be listed in column 5, then it's COLUMN()-4

**Grimace** · 05-05-2009, 01:03 AM

thanks for the prompt reply ...

I used a cut down example of the sheet as i read the "dont use sensitive information" warning in the forum, and wanted to also remove a lot of clutter that was not pertinent to the data i was working with. The main sheets is hundreds of rows and many columns, but the standard formula should let me adapt it across the board.

thanks for the clarifications, I will amend my formulas accordingly.
I think it is the column()-6 that is the one throwing it out.

Appreciate your help

Darren

**JBeaucaire** · 05-05-2009, 01:10 AM

Here's another layout that removes all the SUMPRODUCT formulas...a LOT of those in a sheet can actually start to degrade performance...and I've got two per cell in my original solution.

Here's a version that uses a "KEY COLUMN" added in column A that creates an easy to spot unique value for every row, makes doing an INDEX/MATCH a breeze.

I also took the opportunity to show you how a ROW format might be a better layout if the number of races gets long, this would most likely show more onscreen at once.

FYI.

**Grimace** · 05-05-2009, 01:43 AM

thank you for the options.

The Course codes (colours in the example) are the ones likely to be quite numerous, and each course will generally have a max of about 12 races. I assume that the table can work similar transposed to a column format and using the ROW as the index ?

This is how the current format sits and i would keep it that way to save confusion for those using the sheet.

I have attached the actual sheet i am working on for reference.
Column A is the Courses
Column B is the Races at each course
Column D the times for each race

Column L will be where I am selecting the major courses for the day,
and Cells M3 .. X10?? (maybe) is where the times will land.

**JBeaucaire** · 05-05-2009, 02:44 AM

Have a look.

**Grimace** · 05-05-2009, 02:49 AM

Perfect

Thank you very much for all of your assistance.
It is greatly appreciated.

Darren

Thread: Pulling Items from a multiple column Range

Thread Tools

Display

Pulling Items from a multiple column Range

Re: Pulling Items from a multiple column Range

Re: Pulling Items from a multiple column Range

Re: Pulling Items from a multiple column Range

Re: Pulling Items from a multiple column Range

Re: Pulling Items from a multiple column Range

Re: Pulling Items from a multiple column Range

Re: Pulling Items from a multiple column Range

Re: Pulling Items from a multiple column Range

Re: Pulling Items from a multiple column Range

Re: Pulling Items from a multiple column Range

Re: Pulling Items from a multiple column Range

Re: Pulling Items from a multiple column Range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions