?
Solved

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

Posted on 2007-08-05
31
Medium Priority
?
179 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:keilah
  • 17
  • 14
31 Comments
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19634406
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
 

Author Comment

by:keilah
ID: 19634478
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
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19634493
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:keilah
ID: 19634510
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
 

Author Comment

by:keilah
ID: 19634513
do you have a private email adresss.....to emai to.....???

0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19634531
keilah,

Either of those address are fine.

BTW, EE = Experts Exchange

Wayne
0
 

Author Comment

by:keilah
ID: 19634554
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
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19634615
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
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19634625
also, for 228, I get 'Cetelem016 + JDWilliams001', not just 'JDWilliams001'
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19634629
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
 

Author Comment

by:keilah
ID: 19634645
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
 

Author Comment

by:keilah
ID: 19635210
Hi wayne

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

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

thanks

0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19636106
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
 

Author Comment

by:keilah
ID: 19636766
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
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19638416
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
 

Author Comment

by:keilah
ID: 19638654
Hi wayne

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

thnaks once again...
0
 

Author Comment

by:keilah
ID: 19640738
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
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19646283
keilah,

Where is the data you wish to sum?

Wayne
0
 

Author Comment

by:keilah
ID: 19646338
ok can i post the spreadsheet somewhere for you to view...........
0
 

Author Comment

by:keilah
ID: 19646494
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
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19646605
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
 

Author Comment

by:keilah
ID: 19646666
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
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19646723
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
 

Author Comment

by:keilah
ID: 19646817
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
 

Author Comment

by:keilah
ID: 19649095
Hi wayne

Any luck with the vba code???
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19651305
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
 

Author Comment

by:keilah
ID: 19652055
Hi mate

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

0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19654146
>> I await your update.....

Which update?
0
 

Author Comment

by:keilah
ID: 19654213
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
 
LVL 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 2000 total points
ID: 19654260
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
 

Author Comment

by:keilah
ID: 19654409
Hi wayne

Post and credited you the original points....

thanks for the feedback to date.....excellent all round...
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question