Copy data from broken list in a column to a row

[jaffermo]

Hello all

I'm a total novice at macros and I'm currently working on creating a more efficient reporting process for my team. I work for a bank and am responsible for managing operational risk and processing on options trades.

I have a report where trade details and each trades portfolio number is listed in rows. Each portfolio might have one or more trades and some days we might have some portfolios that might not be there other days. Basically, I need a code that can go down the column to the end of the data and copy the different portfolio numbers into a specified row where a formula under that row will count how many times that number is repeated in that column. Please refer to attached for an idea of what I'm looking for and thanks in advance for your help.

Maurice

[jaslake]

Hi jaffermo

Welcome to the Forum.

How many portfolios could there potentially be?
You mention

copy the different portfolio numbers into a specified row

In your sample file that's row 24. Is the specified row 24 or is the specified row "the 3rd row below the end of the data"?
Will the portfolio numbers be in column G and start on row 2?

[jaffermo]

Hi jaslake - the number of portfolios we treat in our daily workflow can range from 2 or 3 to 30..it's never the same number of portfolios daily.

The specified row will always be the 3rd line after the last line of data. And yes, the portfolio numbers will always start in G2.

Thanks for looking into this!

[jaslake]

Hi jaffermo

This code is in the attached and appears to do as you require.

Please note that the code requires you to set a reference to Microsoft Scripting Runtime... Tools > Reference > Microsoft Scripting Runtime

You'll need to think about how to implement...I don't know your process. The code can be adapted to whatever that process is. Let me know of issues.

Please Login or Register  to view this content.

[jaffermo]

Jaslake - don't know how to thank you. It works so smoothly and doesn't interfere with data around it. I doubt there's any computer or excel related advice I can give you but if you're ever in NYC, don't hesitate to look me up. Drink's on me

[jaslake]

Hi jaffermo

You're welcome...glad I could help. Thanks for the Rep.

If that satisfies your need, I'd appreciate it if you'll please mark your thread as "Solved".
To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save

[jaffermo]

done - thanks