[Solved] Searching by rows & columns

Discuss the spreadsheet application

[Solved] Searching by rows & columns

Postby bickle » Fri Jul 04, 2008 2:23 pm

hopefully as normal there is a straight forward answer but my brain is frazzled today and i can't get my head round it.

i have attached a condensed version of what i am trying to do, the row info and and the data will be pulled in from elsewhere i just need to interrogate the table.

hopefully it is clear any questions let me know.

brgds
Attachments
example.ods
(9.29 KiB) Downloaded 263 times
Last edited by bickle on Mon Jul 07, 2008 11:37 am, edited 1 time in total.
bickle
 
Posts: 5
Joined: Sat May 24, 2008 10:46 am

Re: searching by rows & columns

Postby jrkrideau » Fri Jul 04, 2008 4:03 pm

bickle wrote:hopefully as normal there is a straight forward answer but my brain is frazzled today and i can't get my head round it.

i have attached a condensed version of what i am trying to do, the row info and and the data will be pulled in from elsewhere i just need to interrogate the table.

hopefully it is clear any questions let me know.

brgds

I'm not very good with calc so I don't see any easy way to do this. Do you have to have the data in this type of table? It is not ideal for searching. If you could have it in a layout like this (not all the data in included)"
Code: Select all   Expand viewCollapse view
names   dates   values
balhrphx   01/01/08   655
balhrmex   01/01/08   729
balhrsyd   01/01/08   745
balgwjer   01/01/08   111
balgwgnd   01/01/08   528
balhrphx   02/01/08   655
balhrmex   02/01/08   729
balhrsyd   02/01/08   745
balgwjer   02/01/08   111
balgwgnd   02/01/08   528
balhrphx   03/01/08   655
balhrmex   03/01/08   729
balhrsyd   03/01/08   745
balgwjer   03/01/08   111
balgwgnd   03/01/08   528
balhrphx   04/01/08   549
balhrmex   04/01/08   646
balhrsyd   04/01/08   762
balgwjer   04/01/08   128
balgwgnd   04/01/08   545


then you can use a standard filter to do the work. Data > Filter > Standard Filter
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3686
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: searching by rows & columns

Postby bickle » Sat Jul 05, 2008 2:40 am

thks but having all the data listed that way and flitering doesn't do the job i'm after as there is a lot of additional rows to add

i had envisaged a vlookup on column A followed by an hlookup along row 1 to give the value corresponding to the vlookup and hlookup results. i haven't been able to combine these, and am not sure if it can been done this way anyway.

(had also looked at the DGET function which i thought might work but again failed miserably !)

any other suggestions welcomed

best regards
bickle
 
Posts: 5
Joined: Sat May 24, 2008 10:46 am

Re: searching by rows & columns

Postby squenson » Sat Jul 05, 2008 7:40 am

A combination of the MATCH() and INDEX() functions should do the trick. MATCH(value; range; 0) return the position of value in the range (0 means that the range is unsorted). INDEX(range; r; c) returns the value of the cell of the range at the intersection of the rth row and cth column. Therefore, in your case, try:
Code: Select all   Expand viewCollapse view
=INDEX(B2:K6;MATCH(A11;A2:A6;0);MATCH(B11;B1:K1;0))
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: searching by rows & columns

Postby bickle » Mon Jul 07, 2008 11:37 am

thks works perfectly, exactly what i was after.

best regards
bickle
 
Posts: 5
Joined: Sat May 24, 2008 10:46 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests