Report a list line by line based on name criteria

Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.

I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):

This would be the data on sheet 1:

Name Bonus Commision

Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25


What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:

Tom's Sheet:

Tom $45 $50
Tom $60 $50
Tom $90 $25

Mary's Sheet:

Mary $25 $75
Mary $30 $80


I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.

Is this possible to do in Excel without using a macro? If so, how?

Wow! Thanks. I think that may help. I just have to figure out how the formula works. I have done arrays before, but I haven't worked with ROW, INDEX, or SMALL before so I will have to figure what those functions do. Also, I wasn't sure you could do an array with cells from a separate sheet. Either way, thanks a lot for the help. I think this is enough to figure it out. I will post something back to this thread if it doesn't work.

Looking for a challenge?

Well... I tried. I spent quite a few hours trying to figure this out and I must be messing up with the ROW() info or something. I really can't figure out what I am doing wrong. I believe I understand the formula, but obviously I went wrong somewhere.

Can anyone take a look for me? I have attached part of the workbook I am working on. I have copied the formula from the workbook Dean attached and modified it to work for my situation. If you look at the workbook, look at the column that says "Dealership". It is giving me funky info.

Here is the array formula from Dean's attachment:

{=IF(ROW()-1>COUNTIF(Sales!$A:$A,$A$1),"",INDEX(Sales!$B$1:$B$1000,SMALL(IF(Sales!$A$1:$A$1000=$A$1,ROW(Sales!$A$1:$A$1000),""),ROW()-1)))}

Here is the one from mine:

{=IF(ROW()-8>COUNTIF(Main!$D49:$D1514,$L$3),"",INDEX(Main!$B$49:$B$1514,SMALL(IF(Main!$D$49:$D$1514=$L$3,ROW(Main!$D$49:$D$1514),""),ROW()-8)))}

I hate having to ask, but I am just stuck... I would normally be able to figure this one out on my own.

I see what you are saying, and it will work if you have the data from the top down, though that won't work for my situation since I have all the other info at the top of the Main page and need to keep the data down below. I thought maybe changing the ROW()-1 to something like ROW()-8 would work, but it didn't.

Is it not possible to do an array unless all the data starts at the top of the page? I don't quite understand.


Also, I am hoping this isn't an issue:

I shortened this example to be able to upload it. The file attached to this will show that the information on the Main page will have gaps in it on each column. I understand that the array is to just look for info and then display the criteria in that row.

Does anyone have any ideas to help me out?

Yes. The one attached to #6 is similar to #4. I just added one extra area to the sample to show that the data format will repeat itself all the way down the page to line 1514. Then data will be pasted into those cells.

My problem is that I can make this array on the the installer1 sheet work if everything starts at the top of the pages, though I don't have that option in this case. For some reason I can't make the formula work in this format. Is there a way to make it work in the format I have it in? I feel that I am messing up with ROW()-8 area.

Any help from anyone that is familiar with this situation would be much appreciated. Thanks.

I decided to post this so that anyone searching later will be able to see the answer. I finally figured out where my problem was.

My original formula:

{=IF(ROW()-8>COUNTIF(Main!$D49:$D1514,$L$3),"",INDEX(Main!$B$49:$B$1514,SMALL(IF(Main!$D$49:$D$1514=$L$3,ROW(Main!$D$49:$D$1514),""),ROW()-8)))}

The modified one that ended up working:

{=IF(ROW()-6>COUNTIF(Main!$D49:$D1514,$L$3),"",INDEX(Main!$B$49:$B$1514,SMALL(IF(Main!$D$49:$D$1514=$L$3,ROW(Main!$D$49:$D$1514)-48,""),ROW()-6)))}


The problem was with the ROW functions. It was not grabbing the proper rows in order to display the data. Since I started at row 7, I had to use ROW()-6 in the formulas for the Installer sheet and since I started at row 49 on the Main sheet, I had to modify it to say ROW(....)-48.

Thank you to everyone that helped me with this problem. I can appreciate sites like this where people take their own time out to help others.