Excel 2007 : Formula to find specific data and then insert it into next available row

[mini_cam]

Hi,

I am having difficulties working out a formula to do the following, so any help would be much appreciated:

I'd like to search for data in a different sheet, and then for it to automatically insert each entry into the next column. I will be continually adding data into the primary spreadsheet, so I would like it to continuously update.

For example:
Currently the data set would look like this:
John 100
Mary 90
James 95
John 92
Simon 100
Mary 120

I would like the formula to find the data and then display it like this, adding entries that match the criteria:
A B C
John 100 92
Mary 90 120
James 95
Simon 100

Does anybody have any suggestions of how I could do this automatically?

Thanks very much!

[Marcol]

Try this example workbook

Enter your data in Sheet "Primary" Columns A&B

Sheet "Result" will hopefully return your requested results.

Sheet "Result" Formulae:=
In A2

Please Login or Register  to view this content.

Drag/Fill Down as required

In B2

Please Login or Register  to view this content.

Enter with Ctrl+Shift+Enter
Drag/Fill to right as required
Drag/Fill Down


This will result in blank rows, these can be hidden by filtering the "Names" column for non-blanks.
This will have to be done manually each time the sheet needs to be veiwed as a continuous list, It will not update manually without VBa

Hope this helps

[mini_cam]

Hi Marcol,

Thanks for your work! I'm nearly there, but a few slight errors I can't work out ...

The first column (the names) are fine.

When I put the second column in, the numbers, they are always one row below. For instance, the original sheet ("Shop") has it laid out like:

John (F6) 100 (G6)
Fred (F7) 90 (G7)
Bill (F8) 120 (G8)

But it displayed like this:
John
Fred 100
Bill 90

The formula I have is:

=IF(A6="","",IF(ISERROR(INDEX('Shop'!$G$1:$G$1001,SMALL(IF('Shop'!$F$1:$F$1001=$A6,ROW('Shop'!$F$1:$F$1001)),COLUMN('Shop'!A$1)))),"",INDEX('Shop'!$G$1:$G$1001,SMALL(IF('Shop'!$F$1:$F$1001=$A6,ROW('Shop'!$F$1:$F$1001)),COLUMN(A$1)))))

What have I got wrong?

It also doesn't seem to drag across as they appear blank, although there are multiple entries for the same name.

Thanks a lot!!

[Marcol]

I can't wholly replicate the error you describe, however I have reworked the demo workbook to the layout I think you are using.

The only obvious error in your formula is
=IF(A6="","",IF(ISERROR(INDEX('Shop'!$G$1:$G$1001,SMALL(IF('Shop'!$F$1:$F$1001=$A6,ROW('Shop'!$F$1:$ F$1001)),COLUMN('Shop'!A$1)))),"",INDEX('Shop'!$G$1:$G$1001,SMALL(IF('Shop'!$F$1:$F$1001=$A6,ROW('Sh op'!$F$1:$F$1001)),COLUMN(A$1)))))

There is no need for apostrophes when the sheet name is all one word

I can only assume that your row references were somehow wrong.

If you cannot get your workbook to work using this sample, post another workbook showing the layout you are actually using.

Hope this helps

[mini_cam]

Thanks a lot for your help!

For some reason it wouldn't work properly when I did it in my workbook, however when I copied the whole sheet from your spreadsheet into my one, and then adjusted the formula to suit, it worked!

Thanks.