[URGENT HELP REQUIRED] Macro to group informtion together

Hi,

Please refer to example spreadsheet attached.

I am needing to create a list of unique profiles i.e profiles that contain the same holdings. Is it possible to create a macro that will give me this list. Profile 1 and Profile 4 are the same and Profile 2 and Profile 5 are the same. Is there a quicker way of being able to identify this as I have a list of 800 to work through.

Thanks.

Do you wish the result like this? If not, could you tell how the result looks like

Profile Name Code of Holdings Holding Name
Profile 1 AAA Holding A
Profile 1 BBB Holding B
Profile 1 CCC Holding C
Profile 2 DDD Holding D
Profile 2 EEE Holding E
Profile 2 FFF Holding F

I have no set way in which I need the result to appear. I just need to be able to show what Profiles have identical holdings. Coulmn B (Code of Holdings) are unique and can be used for comparison. Is it possible to list Profiles that contain Holding A, Holding B & Holding C? But, if I have several hundred different varities of what holdings a Profile might contain is it possible to type into somewhere what holdings I'm wanting to search for and list the Profiles they are included in? Any suggestions or help would be much appreciated.

Thanks

It is your data and you know what format the data should look like. I think it would be easier if you could show us your desired result with the attached book. How do the data look after running a code?

Here is an example of what I would like it to be like, but I dont know how easy it is to get it into this type of format. Is it possible to have a macro to arrange it the way I have it formatted on the spreadsheet?

By 'packages' i mean grouping a specific set of holdings together and see what Profiles contain these holdings. This was just an idea of how the resluts could be formatted.

Yes it would

Hi arlu1201,

Have you had a chance to look at this?

Sorry, I think I'm explaining this poorly. I'll try again

The orginal list is a shortened size of what my actual spreadsheet looks like. I have about 800 Profiles to look at - this list only contains 5. The problem I have found with my big list is that some Profiles are identical (by means of containg the same holdings) but differ from Profile Name. i.e. Profile 1 & Profile 4 are identical - they have the same holdings but different names.

Is there a program or macro to use by which I can tell excel "search for everything in the list that contains specific holdings and tell me what Profiles use these holdings".

I am just needing a way to get a list of Profiles that contain the same holdings.

Is it possible to run a macro that will come up with a pop up menu. Within the pop up menu will give me the option of typing what holdings im looking for. From that it can search through the spreadsheet to give me a list of all profiles that contain the same holdings?

Hi arlu120,

Do you know a code that can do what I've posted above?

This is similar to what I'm looking for. Did you get an answer Eoghan? Would be really grateful for any help!

Hi Arlette,

I appreciate you may be busy with other things and have told me not to 'bump' you with messages so often but I'm desperate to find a solution to my problem above. It would be very helpful if you can try and help soon.

Thanks,

Eoghan

Hi Arlette,

Thats brilliant thanks. Is there a way to search for multiple holding rather than just one? There is not a specific amount of holdings I need to look for. So, is it possible to include in the macro a pop up box in which I can enter how many holdings i need to look for and from that abnother pop up box to appear in which i can type the holdings in. From this the macro can look through the spreadsheet and create a list in a Report tab like you've already done so.

Thanks

Hi Arlette,

This is not really what im after. The first macro seems like its on the right line but I'm needing to list profiles which have the same underlying holdings. So for example, I could search for Profiles that contain holding a, holding b, and holding c and it would give me a list of Profile 1 and Profile 4.

Yes that would work also. Can I use one of the macros above to do this?

I have attahced a updated spreadsheet. In the 'Holding Info' tab is where I can enter the holdings I am wanting to search and then reduce a reoprt tab similar to the one you created in your first macro? Is this possible?

Hi Arlette,

I tried the above code but I complile error appeared on the following line:

, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

The error message is saying 'variable not defined' if that helps...

I got it so you want to "filter"your list depending on the holding code

You can do it with the filtering option yet I soon give you the code to run

in sheet2 I highlighted where you can enter the holdings code
after that if you press ctr R the matching records will appear below

there is now a run-time error 438 with the following code, stating 'object doesnt support this property or method':



.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Hi Arlette,

Perfect. Is there a way to create a different worksheet when I type different information into the holding tab? Currently a 'Report' tab is created but when I change the search criteria it overwrites what was already there.

Thanks

Hello rcm,

I tried your workbook. It works perfect with the examples provided in it.
However i tried to enter a profile called ''Profil 6'' with the code ''NNN''. The results i obtain after running the macros contains profile 6 as well as other profiles with different codes...

Hi Arlette,

That's brilliant, thanks. I added Profile 5 which contained Holding A & Holding B and Profile 6 which contained Holding A & Holding B. When I used the macro Profile 1,4,5 and 6 appeared. Is there a way to change the coding so that it has to be an exact match i.e. Profile 5 & 6 would be the only ones to appear?

Naja, I added 'Profile 6' to Cell A14 of 'Sheet1', 'NNN' to B14 and 'Holding N' to C14 and in the 'Holding Info' tab changed the search criteria to Holding N i.e. I inserted 'Holding N' into A2 and kept everything blank bar the column headers i.e. 'Holding 1', 'Holding 2', etc and using the macro Arlette provided it worked perfectly...

Hello Eogh,

You mean the code that is in message 38? I used the excel file in message 34, is that correct?

Yes, use the code in messgae 38. I haven't tried the method posted in messgae 34.

Hello,

I mean using the code on the excel workbook in message 34.
On what excel workbook did you try it on?

Use it on this spreadsheet

Thank you Eogh

Hi MarvinP, thanks for that. Is there a way to change the Pivot table to search for multiple code of holdings. i.e Profiles that contain AAA, BBB & CCC?

Hii Arlette,

I don't think the method MarvinP has suggested will work well for the list I have. Could you anwer my query on message 41? You're macro seems to be the best way round it but I just need it formatted to what my spreadsheet looks like. I've posted an example spreadhseet on message 41 for your reference. Thanks

I=1246, j=1

I had a look at row 1246 and the code is '#N/A'...this is the first one that appears in the list. Could this be why?

It seems to be working fine now. I have 10 fields in which I can use to search underlying holdings. The macro is currently set up for 5. How can I change it for 10?

Is there a limit on how many Report worksheets can be created? It doesn't work when I'm trying to create Report 11. The error states - 'Run time error '1004': Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referecned by VB.'

Is there a way of changing the coding so that I can have more than 10 reports?

Excellent. Thanks very much for all your help on this thread. It has been a real help!

So I put this code in the excel worksheet attached to post 41?

Naja, yes put this code into the spreadsheet on post 41. This macro will allow you to create more than 10 reports.

Hi Arlette,

Its working brilliantly so far the only problem I'm finiding with it is if im looking for 3 holdings it will list profiles that contain these 3 holdings but also profiles that may contain more than 3 holdings. For example if Profile 1 contains AAA, BBB, CCC and Profile 2 contains AAA, BBB, CCC and DDD and I search for AAA, BBB, CCC it will list both profiles. I need it to be an exact match. Is there a possible way of doing this?

Have you had a chance to look at my query above?

Only AAA, BBB and CCC...I would only want Profile 1 to appear as it is an exact match. Although Profile 2 contains these holdings it also contains holding DDD. Its an exact match that I'm after

Hi Arlette, unfortunately this hasn't worked. Same problem still occurs...

Hi Arlette, have you had a chance to look at this problem?

Hi, been away all weekend so havent had a chance to look at the code until now. Working perfectly...thanks again for doing this. Its been a big help

Hi Arlette,

Unfortunately it doesn't seem to be working. It worked on the first couple of examples I did but then the past few it hasn't worked. Sometimes it doesnt return anything, sometimes it returns an incomplete list. Any ideas for fixing it?

Unfortunately not - it's got data on it I need kept private. Do you want me to go into more depth about the problems?