Develop a NEW Macro.......Important.

Hello once again.....second question

Ok i have a workbook with one work sheet. I need to creat a macro the consolidates or combines data from say col A i.e if have the following (Barclays 1 (row 1) and Barclays 2 (row 2)) same column.....

to the following format in column B (barlcay 1+2) - also at the same time sum'ing the col's and row's that have any figures in ...............

i can forward the spreadsheet if this helps......

regards
keilahAsked:
Who is Participating?
 
Wayne Taylor (webtubbs)Commented:
keilah,

I havent really had a good look at it yet, but yes, you should ask another question as it involves a seperate macro from the one asked in this question.

When posting you new question, be sure to include the link to your file (in case any other expert comes along).

Wayne
0
 
Wayne Taylor (webtubbs)Commented:
Hello keilah,

At present, it's not possible to attach files directly to the question. You can, however, upload them to a free hosting site, and post the link to that file in a new comment.

There is also http://www.ee-stuff.com/Expert/Upload/upload.php. You will need to log in with your EE username and password.

Regards,

Wayne
0
 
keilahAuthor Commented:
the original question is posted in my profile keilah: under tat same title.

However. once again......What i am trying to do is merge/ consolidate or combine data. See spreadsheet

In col A i have proftfolio data, col B individual ids for that data. In col D Combined id for new combined data. and finally that combined data in col e.....ie the finished product of data from col A combined.

What i need is this happening through vba, and also all row and with a set range of data to sum across for this new combined data in col E

Col A -                  Col B                       Col D                                Col D
portfolio Name     Portfolio Id         Combine Portfolio ID      Combined   portfolios
Barlcays1                  2                               2                         Barclays 1+2
abbey001                 3                               3                         Abbey001
barclays2                  2

in the abbey001 i do not need to combine this data......


I hope i have explained this properly.

thanks
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Wayne Taylor (webtubbs)Commented:
keilah,

Where did you upload the spreadsheet to? Can you post a link to it here?

Alternatively, you can email it to me (my email's in my profile - click my username above), and I can upload it for you.

Wayne
0
 
keilahAuthor Commented:
which one - sorry for the sill question

For EE concerns -  webtubbs <AT> e-e <DOT> com
QuickEE or Other - webtubbs <AT> gmail <DOT> com

thanks...
0
 
keilahAuthor Commented:
do you have a private email adresss.....to emai to.....???

0
 
Wayne Taylor (webtubbs)Commented:
keilah,

Either of those address are fine.

BTW, EE = Experts Exchange

Wayne
0
 
keilahAuthor Commented:
Ok finally

I have up loaded the file under  my id (ID:19634513)...here is the meaasge showing this...

Your file has successfully been uploaded!
To download the file, you must be logged into EE-Stuff. Here are two pages that will display your file, if logged in:

View all files for Question ID: 19634478
https://filedb.experts-exchange.com/incoming/ee-stuff/4260-Book1.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/4292-Book22.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/4315-Copy-of-Test-Works-sheet-JanFin.zip
 

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/4260-Book1.zip

i await your email to the spreadsheet.

thanks
0
 
Wayne Taylor (webtubbs)Commented:
keilah,

I have your file. Thanks.

Now, I'm guessing you wan't to automatically generate column E? If so, I've noticed a couple problems....

105      HSBC1      
106      HSBC2      
107      HSBC3      
108      HSBC4      
109      HSBC5      
110      HSBC6      
111      HSBC7      
112      HSBC8      

...all these do not have a corresponding ID in column B. Where did you get the "CombonedData" from?

The rest I can do, although it'll return something like 'Barclays1 + Barclays2' instead of 'Barclays1+2'. Is this OK?

Wayne
0
 
Wayne Taylor (webtubbs)Commented:
also, for 228, I get 'Cetelem016 + JDWilliams001', not just 'JDWilliams001'
0
 
Wayne Taylor (webtubbs)Commented:
This is the macro I used, btw....

     Sub GetCombinedData()
         Dim r As Long
         Dim fnd As Range
         With Worksheets("Sheet1")
             For r = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
                 Set fnd = .Range("B:B").Find(.Cells(r, "D"), , xlValues, xlWhole)
                 If Not fnd Is Nothing Then
                     Dim fAdd As String: fAdd = fnd.Address
                     Do
                         .Cells(r, "E") = .Cells(r, "E") & .Cells(fnd.Row, "A") & " + "
                         Set fnd = .Range("B:B").FindNext(fnd)
                     Loop While Not fnd Is Nothing And fnd.Address <> fAdd
                     .Cells(r, "E") = Left(.Cells(r, "E"), Len(.Cells(r, "E")) - 3)
                 End If
             Next
         End With
     End Sub
0
 
keilahAuthor Commented:
Ok.....some more information.

1. Yes i want to automtically generate col E, from the data in col A.

2. One point the combined portfolio id's - are simliar to the individual id's in Col B.... just a quick heads up....

3. Another point the combined id portfolios only apply to the Barclays portfolios ALL other portfolios are not combined and are the same in col A and col E.

4. I am guessing to difficult to do Barclays 1+2........i am happy with your format....(barclays1 + barclays 2)...if this is th ebest solution.

5. The combined Data id - these are fixed in the excel spreadsheet.....i will be giving i have not control over this col.......


thanks.....once again.....
0
 
keilahAuthor Commented:
Hi wayne

Any developments on the macro.......

I have also added another quest..........final one.

thanks

0
 
Wayne Taylor (webtubbs)Commented:
keilah,

Yeah, sorry. I had to get some sleep :)

A couple more questions.

1) What do you mean by point 3 above? In the spreadsheet, there are a number combined that aren't Barclays....
 
    CO-OP1+2
    CapitalOne001-002      
    HBOS001-004      
    T-Mobile001-004      
     
 How were these combined?

Wayne
0
 
keilahAuthor Commented:
Hi Wayne.....yes we all need some sleep........

I guesss triedness on my part toooo...."what i ment to say is that the combined formula manly applys to the barclays portfolios", however, i was wrong as you have rightly pointed out.......

I also need those combined to.............

"what happens once a account is near the end of payment we combine the portfolios together to free up sapce of new portfolios,,,,,,if this helps...."

thanks.......i await your reply...
0
 
Wayne Taylor (webtubbs)Commented:
OK. Have you tried out the macro I posted above? That just about does it, but if there's something it doesn't do, let me know and I can fix it.
0
 
keilahAuthor Commented:
Hi wayne

just in the middle of something and i'll get back to you ASAP>...

thnaks once again...
0
 
keilahAuthor Commented:
Hi wayne the code works.....

Can you amend it if it is not to much trouble....and 'll post it again as a fresh request. 500 points again.

to sum the row of the combined data so:

barclays1       500            barlcays 1+2     1250  etc.....
barclays2       750      

if you want me to re post message me back......

thanks.


0
 
Wayne Taylor (webtubbs)Commented:
keilah,

Where is the data you wish to sum?

Wayne
0
 
keilahAuthor Commented:
ok can i post the spreadsheet somewhere for you to view...........
0
 
keilahAuthor Commented:
Hi Mate spread sheet is here..........But before you do any unnecessary work......I have a better idea.......the other macro works a treaty.....

Ok Look at SHEET 3......

I have created a pivote table that show the result of say Barclay 1 + barclays 2 sum togther.........on the left of this table is the combine portfolios col F, next to a sum col G empty..........

Ok -here goes is it possible to match exactly and extract the sub total per portfolio group (i.e id 1 = barclays 1 +2 ) as per pivote table and post this as a total figure into the sum col G.

with the option of the user saying which column and the option of stating the work sheet if i move the sum column to a different sheet.

thanks....any question just email back.............


Your file has successfully been uploaded!
To download the file, you must be logged into EE-Stuff. Here are two pages that will display your file, if logged in:

View all files for Question ID: 19634406
https://filedb.experts-exchange.com/incoming/ee-stuff/4262-Book2.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/4279-Book1.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/4311-Doc1.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/4392-Bookp1.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/4393-Bookf1.zip
 

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/4279-Book1.zip 
0
 
Wayne Taylor (webtubbs)Commented:
keilah,

Is the Pivot Table necessary? Also, I take it each portfolio has a value of 1000, or is that just a sample? Is the values to sum stored anywhere else?

Wayne
0
 
keilahAuthor Commented:
Hi Mate,,,,,

i would have to say yes to the pivote table ONLY this time..............the 1000 value of the portfolio is just a sample as you guessed. if the process is to diffcult with the pivot table "just message back, can look at other options"..

yes, the sum value is going to be stored ina different worksheet, called "fees" and added to column d row 5 start point end at row 249 same column.



0
 
Wayne Taylor (webtubbs)Commented:
keilah,

OK, if it's stored in a seperate worksheet, I can use that. Do you want this summing to happen at the same time as the combining of the portfolios?

Wayne
0
 
keilahAuthor Commented:
Hi mate

in this one not interested in combine the portfolio.....just an exact match and transferring the "matched sub-totals ap per id" - see pivote table to a fixed column with already combined portfoilo in it......"column B", sheet called "fees"

i have already used one of your other vba code for that element.....just need the code here for the comestics......tying up...........not all at once. Mate....

i need to do other stuff to the worksheet.....thanks alot...
0
 
keilahAuthor Commented:
Hi wayne

Any luck with the vba code???
0
 
Wayne Taylor (webtubbs)Commented:
keilah,

This one is going to be a little trickier.

Also, because it involves a seperate macro, it should probably be asked in a seperate question.

Wayne
0
 
keilahAuthor Commented:
Hi mate

thanks for the feedback....Not a problem. I await your update.....

0
 
Wayne Taylor (webtubbs)Commented:
>> I await your update.....

Which update?
0
 
keilahAuthor Commented:
Hi wayne

keilah,

This one is going to be a little trickier.

Also, because it involves a seperate macro, it should probably be asked in a seperate question.

Wayne

Your posting yesterday........as above....just wounder if you have managed to crack the VBA to the problem? Unlesss you want me to close this question to you and ask a second question.....for the seperate macro.......what should i post, for this question.....

thanks
0
 
keilahAuthor Commented:
Hi wayne

Post and credited you the original points....

thanks for the feedback to date.....excellent all round...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.