Vertically look up multiple row entries with different field entries

I'm going to use a hypothetical for this because I don't know how to explain it lol

I'm a receptionist and I keep a database of clients, each with a number. Some visit my business regularly, others not so regularly. Every time they visit I enter their number and what they purchased when they visited. I have around ten fields of items, and 300 visit entries. The clients bought differeny things every time.

Is there a way for me to type a client number into Sheet 2 and have all the visits and purchases from that client pop up?

Thanks for any help

Hi Pete, thanks heaps for that

I have yet to look into those sums, I won't ask you to explain - I should be able to figure it out

I thought it'd be a good idea to clarify exactly what's in my spreadheet so far, though

On the first sheet I have client visits
On the second sheet I have a list of over 2000 clients
On the third sheet I have used VLOOKUP to compile client inormation from sheet two (so I just throw a client number into the specified cell and it shows me all the fields).

So I'd like to get individual visits per client and what they purchased into either sheet 3 or sheet 4. Sheet one goes to column R, with column A as dates and column B as client numbers. Then just data per field from there.

I'm going to have a go at what you've suggested here after some Googling, but does my explanation change anything with your answer?

Thanks heaps Pete : )

I tried the first equation in S2 and I got an error, it says #NAME? I suspect I'm supposed to have something inbetween the quotation marks ? Sorry if this is a stupid question lol

Hi Fotis,

I'm using my iPhone, I'm happy to answer any questions in detail

In the first sheet I have set it up so the date comes in column A and the client number in column B, then some fields are money, some are a 'y' for 'yes', some are numbers (not currancy), some are just notes. We provide specific services so these services are defined in row 1, and recorded in the fields.

Some clients visit more than once - I'm looking for a way I could type the number (after setting up a sheet for the visit details) and Excel filters that number's visits, showing me all the rows from sheet 1 with their number in column B..?

I've just noticed that I had missed quotes from around the underscore in the first formula - that explains why you got the #NAME error. I've corrected the formula in my first post, so try it again, although your references will probably be to column B, like this:

=IF(B2="","-",B2&"_"&COUNTIF(B$2:B2,B2))

as you say that is where your client number is. If the formula is in S2, then you will also need to amend the references in the other formulae that I gave you from column L to column S.

Hope this helps.

Pete

Hey Pete

Thanks again for your help on this, I think I'm getting somewhere

I've got the formula in Sheet 1 working, I have a client number in k1 sheet2, i have the l2 formula in S2.. I'm trying this out on the first ten entries in the Daily Sheet

I've got;

=IF(B2="","-",B2&"_"&COUNTIF(B$2:B2,B2)) in column S sheet1

=IFERROR(MATCH(K$1&"_"ROWS($1:1),'Daily Sheet'!L:L,0),"-") in S2 sheet 2 (this one opened the file thing when I pressed enter?)

=IF($L2="","",INDEX('Daily Sheet'!A:A,L2)) in A2

I have a client number in K1 sheet2

Nothing is showing up? Have I missed something?

I think I understand about half of what I'm asking it to do now lol

Just realised that was a stupid question!

I think I got it! Just gotta play around a little more, if I have any questions I know where to come

Thanks so much!!

I've already got stuck

I've dragged all the sheet2 S-column formulas down and the A2 formulas down and accross, and it's showing me all the visits, not just the visits for that client?