lookup multiple records with one criteria

The formula I used is formula array, you need to get out of the formular bar by holding down Ctrl + Shift keys and press Enter after you paste the following:

Formula in cell E2

=IF(COUNTIF($A$1:$A$6,$E$1)>=ROW(A1),INDEX($A$1:$C$6,SMALL(IF($A$1:$A$6=$E$1,ROW($A$1:$A$6),""),ROW(A1)),COLUMN(A1)),"")

It's already in the attached.

Good luck!

Jindon

Hello everyone.

I've been away from the forum for quite some time, but I hope to be back for good soon, to continue my Excel education.

I'm replying to this thread to ask if there are any other methods using formulae to achieve this kind of result? My brain's a little rusty at the moment.

The scenario that I have is this . . .

I have a database containing client details. Each new transaction is recorded on a new row in the database with the client name being in column A. There may be more than one entry for a client in column A but with different details in columns B through to O.
What I'd like to do is to have a separate sheet in the workbook that acts as a display page, whereby when a client name is entered into a cell (e.g. A1) a list of all transactions for that client appears underneath.
I don't particularly want to use Excel's filtering functions or VBA. I've achieved the desired effect using the suggested formulae in this thread (many thanks to the author), but as this solution uses array formulae it slows down the processing of the workbook.

So, in a nutshell . . . is there a way to achieve my goal without the aid of filtering functions, VBA, array formulae, or double-sided sticky tape?

Thanks in advance,

Paul.

PS. It's good to 'see' you all again.

Welcome back.

Sub RetrieveAllRecords(strRecordID as string)


'    Local Variables
     dim rngData as range
     dim rngDataOut as range


     set rngDataOut = Worksheets("DataDisplay").Range("A3")


'    Step 1 : Loop through all records and extract desired records
     for each rngData in worksheets("DataBase").Range("A:A")
          if rngData = "" then exit for
          if rngData = strRecordID then
               rngDataOut.entirerow = rngData.Entirerow.Value
               set rngDataOut = rngDataOut.offset(1,0)
          end if
     next rngData


End Sub

The above code should do what you want. You'll either need to call it from another routine and pass the record id to search for. Or you'll need to rewrite it so that it gets the data internally. This code written right here so it hasn't been tested.

And once again weclome back.

Ok so I see that you didn't want a VBA approach. May I as why not. It will typically run better than a formula base approach.

However, if you want a formula based approach follow this algorithm.

1. Database HAS to be sorted.
2. Do a countif function to determine how many entries that the entered
record ID has.
3. Using the Match function determine the offset to the first entry for that
particular record ID.
4. Then using the INDEX function to retrieve the data from the specified
record and field.

Why this approach. The display page will need to have many rows of formulas ( need to account for a record ID which shows up alot ) then each field or column needs to have it's own formula. Then everytime the page changes or even the workbook changes ( and you haven't turned of the manual calculation ) all of these formulas will need to be recalculated. Then what if the size of your data range changes or goes outside the bounds of your formulas.

The VBA approach is a much better solution. You can add a button and assign the code and the display list is just a click away.

Regards,

Hi Paul,

The following is a formula-based approach, without the use of array formulas.

Assuming that your database is in Sheet 1, your first row contains your headers, and your data starts in Row 2...

Sheet 2:

A1: enter the client of interest

B1: enter a 0 (zero)

B2, copied down:

=IF(Sheet1!A2=Sheet2!$A$1,LOOKUP(9.99999999999999E+307,Sheet2!$B$1:B1)+1,"")

C1:

=LOOKUP(9.99999999999999E+307,B:B)

D2, copied down:

=IF(ROW()-ROW($D$2)+1<=$C$1,MATCH(ROW()-ROW($D$2)+1,B:B,0),"")

E2, copied across and down:

=IF(N($D2),INDEX(Sheet1!B:B,Sheet2!$D2),"")

Hope this helps!

Quote from Moody Toad

... So, in a nutshell . . . is there a way to achieve my goal without the aid of filtering functions, VBA, array formulae, or double-sided sticky tape?

Well if you don't want to use a function and you don't want to use a VBA macro and a computer doesn't have any use for sticky tape. I guess your stuck to powering down the pc, breaking out the abacus, and dig through those paper files. Ooops that's right we're trying to save the envrionment and we inputed those files into the system.

Just a little humor,

:cheers:

Folks,

The response times on here never cease to amaze me! AWESOME!

Thanks for the responses so far.

iwrk4dedpr - Thanks for your responses and advice. I'm reluctant to use VBA because I really know very little about it. It's on my to-do list but there always seems to be something else to do. If I knew more about it and how to modify the codes I would probably use it, but at this time I don't think that VBA is the best option for me. Your help (& humour) is appreciated though.

Domenic - Thanks for your prompt reply too. It appears to be a formula based solution that doesn't involve arrays. I haven't had chance to try applying it to my particular scenario yet, but I do hope to a little later this evening. Would I be asking too much for you to explain exactly how your solution works? I tend to learn more from laymans terms than from just looking at formulae and trying to figure them out. I understand if you don't have time.

Thanks again folks.

Paul.

Paul,

Basically, the formula in B2 looks at the first row of data (Row 2) on Sheet 1 to see if the value in Column A matches the criteria specified in A1 of Sheet 2. If it does, it flags it with the number one. It then does the same thing for the next row. If it matches, it flags it with the next number, number two, and so on.

The formula in D2 gives us the position within Column B of the first record that matched the criteria. Then for the next row, it tells us the position within Column B of the second record that matched the criteria, and so on.

The formula in E2 returns a value from Column B on Sheet 1 referenced by the position named in D2. Then for E3, it returns a value from Column B on Sheet 1 referenced by the position named in D3, and so on.

Unfortunately, for a better understanding, you'll have to refer to the Help menus and study the spreadsheet. At first glance, it may be intimidating. But as you begin to study it, you'll find that it's not very complicated at all. Also, this approach can be adapted to suit a variety of situations.

Got it working!

Domenic,

You're absolutely right. I've spent the last hour or so modifying your solution to fit my needs and, while I don't fully understand it all yet, it is already much clearer. Apologies for requesting the 'in-depth' explanation, but thanks very much for providing it all the same.

I've got your solution working a treat - NICE ONE!

Thanks very much,

Paul.

Quote from Moody Toad

I've got your solution working a treat - NICE ONE!

Glad to hear you're pleased with it!

Quote

Thanks very much,

Paul.

You're very welcome!

Cheers!

Argument Not Optional

This will shock most of you but I've just been experimenting with the possibility of using a VBA solution for this. I tried using the code that iwrk4dedpr provided in this thread (thanks Barry) but I keep getting the error message 'argument not optional' when I attempt to run the code in my application.

I know less than a little about VBA so would someone be good enough to explain this error and why I'm getting it?

Many thanks,

Paul.

I'm just guessing here but did you pass the strRecordID to the code Barry wrote.... it needs to know what to look for...

here's the originals code

[vba]Sub RetrieveAllRecords(strRecordID As String)

' Local Variables
Dim rngData As range
Dim rngDataOut As range

Set rngDataOut = Worksheets("DataDisplay").Range("A3")

' Step 1 : Loop through all records and extract desired records
For Each rngData In worksheets("DataBase").Range("A:A")
If rngData = "" Then exit For
If rngData = strRecordID Then
rngDataOut.entirerow = rngData.Entirerow.Value
Set rngDataOut = rngDataOut.offset(1,0)
End If
Next rngData

End Sub [/vba]

You'd need to pass the srtRecordID to the macro in some way.... this would be an example of how

[vba]Sub GetRecords()
Dim strRecord As String
strRecord = Application.InputBox("Enter the Record ID", Type:=2)
Call RetrieveAllRecords(strRecord)
End Sub[/vba]

You could also have this code embedded in Barry's macro... I left it as it was though in order to illustrate what you were doing wrong & how you would utilise such code....

Hope this helps

My hat's off to you Moody. Using VBA will greatly expand the possiblities that XL has to offer.

If you still need help on how to integrate the code I had written let me know, and thanks Will for the assist. I should have coded that other part as well.

Regards,
Barry

No worries....

I'd like to see a Coding Toad hanging around so I thought I'd help out

Oh deary deary me . . . I think I might get to like this VBA business!

I have it working . . . very primitive at the moment . . . but very smooth and very nice! DAMN! Need to rethink everything now.

Need lots more help though . . .

Whenever I click the button it asks me to 'enter the record ID', but I'd like it to automatically read from one cell, e.g. A1. I'd also like to have all previous records cleared when the button is clicked for a new search. These requests are probably very easy but could someone point me in the right direction?

Many many thanks to both Will and Barry for their VBA help thus far - nice one fellas.

Paul.

Yep. I'd like to see a coding Toad as well. Only as long as he's happy and hops to it.....

:cheers:

Paul

If the lookup data will always be in cell A1 then use my previous code. I'll repost it with the changes to get the data from a static location.

Sub RetrieveAllRecords(optional strRecordID As String) 
     
     '    Local Variables
    Dim rngData As range 
    Dim rngDataOut As range 
     
    Set rngDataOut = Worksheets("DataDisplay").Range("A3") 
     
     '    Step 1 : Retrieve lookup value if not passed 
          if strRecordID = "" then strRecordID = Worksheets("NewSheetName").Range("A1")


     '    Step 2 : Loop through all records and extract desired records
    For Each rngData In worksheets("DataBase").Range("A:A") 
        If rngData = "" Then exit For 
        If rngData = strRecordID Then 
            rngDataOut.entirerow = rngData.Entirerow.Value 
            Set rngDataOut = rngDataOut.offset(1,0) 
        End If 
    Next rngData 
     
End Sub

I've made the incomming argument optional. This way you can either call the routine from another routine and pass the value or you can leave the passed value blank and it will read from the Range("A1"). You'll need to change the sheet name to the appropriate value.

Keep Hopping

Ok, bit by bit I'm getting this working. I do, however, suspect that this VBA lark might not be quite as easy as you magicians make it look!

So . . . Instead of finding the reference cell in the database and returning the entire row of data associated with it, how can I get it to return only certain columns? e.g. I want it to ignore the first 6 columns on the specified row and return everything after that.

Thanks,

Paul.