Retrieve specific data - challenging

**peter1** · 08-19-2010, 07:44 PM

Hello,

I have a dataset that displays something like this (10 rows x 57 columns):

City1 City2 City3 City4 .... City57
19930 19393 19977 19386 20314
0 20314 0 19393 20416
0 20355 0 20355 0
0 0 0 21167 0
.... .... ..... ..... .... .....
0 0 0 0 0

In a separate sheet, I have arranged the following table (1st column) showing (once) every possible number included in the dataset:

19386
19392
19393
19670
.......
21167

I want excel to look each number in the table immediately above and display next to it all the numbers that show up in the same column in the dataset. For instance, say 19393, it should display in the cells next to it the numbers 19386, 20314, 20355, 21167, etc. as in:

19386
19392
19393 19386 20314 20355 21167 .... [i.e. display each number only once and
19670 not display 19393]
.......
21167

and continue with looking over each column and displaying numbers in the same fashion.

Could anyone help?

Thank you so much!

Peter

**teylyn** · 08-20-2010, 03:41 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!