Return all rows with duplicate numbers in column B

[Yupyup]

Hi All,

I have posted on the forum a few days ago asking about this, and someone had suggested I upload an example book. I think I've attached a sample here...

Example.xlsx

Does anyone know a way to do this?

For those who may not be able to download it...

I want Excel to find duplicate numbers in Column B Sheet1 and return all row fields for that client in Sheet2.

I have Sheet1 with Date field in column A, client number in column B, then Items to column R.

I have marked items clients purchased with a 'y' in the item columns.

On Sheet2 is the same set-up, but with no data.

I want to be able to type a client number into Sheet2 and have all the data for that client from Sheet1 show up in the graph on Sheet2.

Thanks everyone : )

[blobbles]

Hey, not sure why no one has got back to you, check the attached sheet. You can do this with formulae, particularly the VLOOKUP function.

Example.xlsx

[Yupyup]

Hey Blobbles, That looks so good except the date doesn't come up? Also there's four different entries for '4' on Sheet1 and they are all coming up the same in Sheet2? Attached print screen

ExampleSheet2.JPG

[blobbles]

I think you didn't explain well. How do we know which row number you want from Sheet1 if all you specify is the customer number? If you put 4 into sheet 2, which row should we return from sheet1? Row 5, 11 or 20?

I think what you actually want to do is to be able to specify the customer number AND the date, with the information being returned. That's not impossible, but requires a little bit of trickery... will get back to you soon...

[Yupyup]

Hey Blobbles, if '4' was put into Sheet2, I'd want it to return all the rows where '4' is in the B column, no matter what actual row number it is

So if client 3 had visited twice, I'd want Sheet2 to show all the rows where client 3 visited but not where any other client had visited... Does that make sense? I can make a new workbook with what I want as the end result if it'd be easier to work with?

Basically I want to be able to extract multiple visits for the same client (so multiple rows with the same number in B column)

Would this all be easier if the date came in B and the client in A?

[blobbles]

Ahhh, OK then, got you. Can't really use formulae in that case, have to go with VBA. Are you familiar with macros? Actually this sort of thing is usually better in MS Access if you have it :-)

[Yupyup]

I don't know how to use Access lol, I'm actually moving this database from Access to Excel.. Not too familiar with Macros. Have I got myself in over my head?? Uh-oh..... !

[blobbles]

OK, check out the attached sheet, should be what you are after!

Example.xlsm

See if you understand the code, if you aren't familiar with VBA it will probably be gobbledygook!

[blobbles]

Hmmm... moving a database to Excel is generally the wrong way to go. Most user databases start off in Excel and then scale UP to MS Access and further to SQL Server/web based if required. That's what happens in 99% of cases. If you are scaling DOWN, there should be a very good reason for it. If this is just one screen of many that you have to convert, I would recommend sticking with MS Access and upskilling you or one of your staff in how to run it.

[Yupyup]

So so close !! Firstly though, I can't actually see any ... formulas? Am I supposed to be able to see formulas with VBA? If you want to ignore that question you can - an intro to VBA may be a bit full on lol.. How would I swap what you've set up here to another database though?

There's just one thing - when I search a client who has visited, say, 5 times, it will populate those 5 lines, but then when I search a client who's visited, say, 3 times, the last 2 visits for the first client I searched remain there? Is there a way to tell Excel to delete these fields when a number is not in A2 Sheet2 (so I can clear the fields after searching one client)?

Thanks so much for your help on this :-) :-)

[blobbles]

OK, so, where to start... I think this sheet is what you are after:

Example.xlsm

All of the tricky stuff is done in code in the background. You can view the code by right clicking on the Sheet tab and clicking "View Code". That takes you to a VBA window with VBA code in it. I wrote that code for what you are trying to do. This code activates whenever a value in the sheet changes - this is called "Event driven" - when an event occurs some code runs. You will notice that the code is surrounded by "Worksheet_Change" which is the event that we are coding for. When the Worksheet Changes, we run the code. This is called "wiring an event" - we have wired code to run for an event. Lost yet?

The code does the following:

If the cell changed is the entry cell

loop through all the rows in the source sheet

If you find the corresponding value in the Cust# column

Copy the values from the source sheet

Paste the values in the destination sheet

What we are doing here though is something that MS Access can do pretty much automatically. And this is why I say you should be using MS Access if you have lots of these screens - its designed for doing EXACTLY what you want - you wouldn't have to write any code in MS Access to do this.

What may actually be more useful if you want something simpler is simply using a filter on the column you are after, as in the attached file:

Example 2.xlsx

Use the drop down filter on the Customer ID column and select the Customer number you want. Simple right?

Again though I would plead that you think about your situation before continuing. If you are going from MS Access to MS Excel, that is highly unusual and is very rarely done. So what's your reason for it? If it is only because you can't maintain the MS Access DB through lack of skills, you are going to have a much greater problem (by a factor of 10 probably) trying to maintain a set of MS Excel sheets than a MS Access DB. In such cases it is much better long term to do a course on MS Access, learn the skills required and use these to maintain/enhance the MS Access database. Trust me, I have seen this literally dozens of times, you will end up in a complete mess and a world of pain if you convert from MS Access to Excel simply because of a lack of MS Access skills.

[Yupyup]

I think I'll take your word for this one. I'll need to learn how to use Access. Drop-down boxes will work for the time being. Thanks so much for all your help on this, I was hoping it could be done on Excel but I was prepared to bite the bullet! Do you know any good websites that could help in learning Access basics?

Thanks again :-)

[blobbles]

Actually, this one looks pretty good: http://www.gcflearnfree.org/access2010/1