Listing of data from database

[gilbert]

Can anyone please share with me on how to go about this......

I will have a sheet of database - about 1000 rolls and 7 columns

Column 1 would be the coding
Column 2 - 7 would be the data in numeric form (from 1 to 49)

Eg.

A B C D E F G
T49 5 13 20 32 39 46 -
T50 2 8 12 29 37 49 | incremental in value sequence
T51 1 4 7 12 37 40 -

I want to create a search field in another sheet (sheet 2), whereby
user just key in the number into each of the 6 cells (duly boxe
up).....Excel would list out those number appeared in the database (i
sheet 1) at the bottom of the boxes showing the code that the numbe
appeared.

eg.
if user keyed in :
B C D E F G
4 10 12 29 37 45
excel would list all the coding that matched the number appeared i
that cell.....in this case ... excel to list out :-

B C D E F G
4 10 12 29 37 45
N/A N/A T50 T50 T50 N/A
- - - - T51 -

Basically, Excel will list out all the number appearing at the sam
column in sheet 1 but it will show the coding rather than th
number.....

I hope I have make clear my question / problem.....and really lookin
forward to some helps

 

[Domenic]

Hi,

Using your sample data, put the following formula in Sheet 2, Cell B2,
and copy across and down:

=IF(ISNUMBER(MATCH(B$1,Sheet1!B1:G1,0)),INDEX(Sheet1!$A$1:$G$1000,ROW()-1
,1),"N/A")

Adjust the formula according to your actual range.

Hope this helps!

 

[Stephen Dunn]

Hi Gilbert,

From my interpretation of what you describe, and what you give example of, I
think this is what you require. It doesn't quite tie up with your
description, or example, but I can't make much more sense of your query.

In A2 of Sheet2:

=IF(COUNTIF(Sheet1!$A$1:$G$1000,A$1)<ROW()-1,NA(),
INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$A$1:$G$1000=A$1,
ROW(Sheet1!$A$1:$G$1000)),ROW()-1)))

as an array formula (hold Ctrl+Shift when you press Enter).

Copy this cell across and down as far as necessary.

HTH
Steve D.

 

[gilbert]

Hi Dominic / Stephen,

Thanks for the help...but it doesn't seem to meet my requirements.....

Dominic's recommendation quite close but it will show if it matc
exactly to the cells in sheet 1.

What I want is Excel able to list down all the coding that match th
number enter in sheet 2 box. If there is no match at all...then it wil
show N/A.

eg..
User keyed in 7,8,10,13, 18,30 in cell B1 - G1

In one attempt, we might be able to get this result :-

B C D E F G
T51 T51 T51 T53 T54 blank
T55 --------blank-------------

The database would look like :-
A B C D E F G
T51 7 8 10 14 15 16
T52 1 2 3 4 5 6
T53 1 2 3 13 16 19
T54 2 3 4 7 18 40
T55 7 30 31 33 36 40

In another word, it will list all the number match to the number use
keyed in matching the correct boxes accordingly. That is, column 1 wil
list all the same number appearing in column 1 of sheet 1 only....eve
the same number appeared in column 3 of sheet 1.

I have difficulties in explaining....but I hope the above elaboratio
would clarify any doubts

 

[Stephen Dunn]

Okay, I think I realise what you're after now, (but if I'm right, that
doesn't explain why Domenic's was closer - so I'm probably wrong...) it only
requires a little change to the original formula.

In B2:

=IF(COUNTIF(Sheet1!$B$1:$B$1000,B$1)<ROW()-1,NA(),
INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$B$1:$B$1000=B$1,
ROW(Sheet1!$B$1:$B$1000)),ROW()-1)))

still entered in array form (Ctrl+Shift+Enter).

Steve D.