Sort data to enable summary report

rickf19

New Member
Joined
Aug 30, 2019
Messages
49
Hi all
I have a spreadsheet from an external source that I need to sort so that we can use the data to create a summary report.

We need to show the data with column headings Name eg Australian dollars, Ticket No, Buy, Sell ,Value for each.

The problem is the spreadsheet we receive has
Australian Dollars at CD R4, 6 AND 8
Ticket No at CD R5, 7 AND9
Buy indicator at CB R3
Value at CL R 5,7 and9
Sell indicator at CB R14
Australian dollars at CD R 15,17
Ticket No at CD R16,18
Value at CL R16,18

There can be numerous Buy /Sell lines for each Heading ie Australian Dollars so whilst the Heading, ticket no. and value are always on the same place ie Heading and ticket no always on same column and 1 line below each other and the value is always on the same line as the ticket no. the Buy/Sell whilst always on Col B is always 1 row above the first transaction heading for each different heading on the report , so one Buy or Sell cell for numerous transaction lines.

Excel data sheet.png


Just a note This is just part of a 6000 plus lines data sheet

Can anyone see a simple ? solution

Thanks

Rick
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
584
Office Version
  1. 365
Platform
  1. Windows
You have a choice of VBA or Power Query or Formulas.
If you use XLBB to attach your sample data instead of a picture I can do some sample formulas.
It would be useful to have the headings which don't seem to be visible.
Essentially:-
In column M if there is a value in column B that does not have the word total in it use the column B value otherwise take the previous value in column M
From Column N on, if there is a value in column C its the first line of 2 lines, bring the values across, then also bring the values across from the next row.
You will wind up with everything on one line.

I am in Sydney Australia and its late here, so if you want to send me an XLBB I will take a look tomorrow,
 

rickf19

New Member
Joined
Aug 30, 2019
Messages
49
Hi Alex
cant do xllb as its a work version of 365 and they dont allow addins.
The column headings are
CUSIPDescriptionUserSec. TypeTrade DateQuantityBrokerPrincipal LocalInt. B/S LocalNet Amt. LocalGain/Loss Local
CancelledTicket#CouponMaturityRatingExch. RateSettle DateOrig. FacePricePrincipal GBPInt. B/S GBPNet Amt. GBPGain/Loss GBP
cols D to P

thanks
Rick
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
584
Office Version
  1. 365
Platform
  1. Windows
Hi Alex
cant do xllb as its a work version of 365 and they dont allow addins.
The column headings are
CUSIPDescriptionUserSec. TypeTrade DateQuantityBrokerPrincipal LocalInt. B/S LocalNet Amt. LocalGain/Loss Local
CancelledTicket#CouponMaturityRatingExch. RateSettle DateOrig. FacePricePrincipal GBPInt. B/S GBPNet Amt. GBPGain/Loss GBP
cols D to P

thanks
Rick
I am just having a look at this now. I don't what your location is / time zone, but it would be helpful to know why the headings and 1st screen shot don't line up.
Screenshot seems to start in column B and finished in Column L, the headings screenshot mentions going from Column D to P.
Ideally we need to start from the original data just as it comes before deleting or moving any columns.

When you first drop the data in does go from Column D to P ?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
584
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hello Rick,

I am hoping that eventhough you don't have XL2BB when you copy this into your Excel the formulas will stick.

You will need to paste it into Cell A1 though, for the formulas to retain the correct cell references.

If my column alignment is out click on column B and insert as many columns as you need to.
The theory is that the left side is the Input section.
  • You should be able to blank out what is there (select then hit delete)
  • Copy in your latest data (you should not have to manipulate anything, it should be a straight copy paste)
  • Make sure that all the formulas to the right of the yellow section cover all the rows (copy them down or quicker is Ctrl+D fill down)
  • The filter the rows on the right selecting Output Detail Line = Yes
  • Copy the result set to another sheet where you are going to work with the cleaned up data.
    I prefer to do Paste Special > Values and Number formats (and have this set up on my Quick Access Toolbar (QAT)
A macro would be even better but this would be something you can do without knowing any VBA.


20210301 Sort Convert Imported Data.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
1
2
3INPUT / IMPORT SECTIONOUTPUT DETAIL LINE YNOUTPUT GRPOUTPUT ROW 1 DATAOUTPUT ROW 2 DATA
4
5DELETE PREVIOUS DATA UNDER THIS LINE - UNDER INPUT COLUMNS ONLY - COPY IN NEW DATAOUTPUT DETAIL LINE YNOUTPUT GRPCUSPIDDescriptionUserSec. TypeTrade DateQuantityBrokerPrincipal LocalInt. B/S LocalNet Amt. LocalGain/Loss LocalCancelledTicket#CouponMaturityRatingExch. RateSettle DateOrig. FacePricePrincipal GBPInt. B/S GBPNet Amt. GBPGain/Loss GBP
6CUSPIDDescriptionUserSec. TypeTrade DateQuantityBrokerPrincipal LocalInt. B/S LocalNet Amt. LocalGain/Loss LocalNOUTPUT GRP                          
7CancelledTicket#CouponMaturityRatingExch. RateSettle DateOrig. FacePricePrincipal GBPInt. B/S GBPNet Amt. GBPGain/Loss GBPNOUTPUT GRP                          
8Buy CurrencyNBuy Currency                          
9AUD0332AAUSTRALIAN DOLLARSCurrency22/11/20201902000JPM1902000Int BS LocalNet Amg LocalGain/Loss LocalYesBuy CurrencyAUD0332AAUSTRALIAN DOLLARS   Currency22/11/20201,902,000.00JPM1,902,000.00Int BS LocalNet Amg LocalGain/Loss Local 29291 11/27/20  27/11/202000.7369881,047,333.81   
102929111/27/2027/11/202000.7369881047333.81NBuy Currency                          
11AUD0337EAUSTRALIAN DOLLARSCurrency22/11/20201902000JPM1902000Int BS LocalNet Amg LocalGain/Loss LocalYesBuy CurrencyAUD0337EAUSTRALIAN DOLLARS   Currency22/11/20201,902,000.00JPM1,902,000.00Int BS LocalNet Amg LocalGain/Loss Local 29427 11/27/20  27/11/202000.7369881,047,333.81   
122942711/27/2027/11/202000.7369881047333.81NBuy Currency                          
13NBuy Currency                          
14Sell CurrencyNSell Currency                          
15AUD0332AAUSTRALIAN DOLLARSCurrency22/11/20201902000JPM1902000Int BS LocalNet Amg LocalGain/Loss LocalYesSell CurrencyAUD0332AAUSTRALIAN DOLLARS   Currency22/11/20201,902,000.00JPM1,902,000.00Int BS LocalNet Amg LocalGain/Loss Local 29291 11/27/20  27/11/202000.7369881,047,333.81   
162929111/27/2027/11/202000.7369881047333.81NSell Currency                          
17AUD0337EAUSTRALIAN DOLLARSCurrency22/11/20201902000JPM1902000Int BS LocalNet Amg LocalGain/Loss LocalYesSell CurrencyAUD0337EAUSTRALIAN DOLLARS   Currency22/11/20201,902,000.00JPM1,902,000.00Int BS LocalNet Amg LocalGain/Loss Local 29427 11/27/20  27/11/202000.7369881,047,333.81   
182942711/27/2027/11/202000.7369881047333.81NSell Currency                          
19NSell Currency                          
20NSell Currency                          
21NSell Currency                          
22NSell Currency                          
Conversion Template
Cell Formulas
RangeFormula
P6:P22P6=IF(OR(ISBLANK(C6),C6="CUSPID",C6="Cancelled"),"N","Yes")
Q6:Q22Q6=IF(ISBLANK(B6),Q5,B6)
R6:AD22R6=IF(AND($P6="Yes",C6<>""),C6,"")
AE6:AQ22AE6=IF(AND($P6="Yes",C7<>""),C7,"")
 
Last edited:
Solution

rickf19

New Member
Joined
Aug 30, 2019
Messages
49
Thank you for the reply I am going to test it this afternoon
I am in UK so GMT
Column headers start at D no headers A to C

Thanks very much again very much appreciated

Rick
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
584
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you for the reply I am going to test it this afternoon
I am in UK so GMT
Column headers start at D no headers A to C

Thanks very much again very much appreciated

Rick
Does that mean that Buy Currency & Cell Currency are in Column C (initial picture they are in B) ?
If this is the case insert 1 column to the left of B in what I sent you assuming you can get it into excel .

To get it into Excel click on the Duplicate button then paste into you excel while on cell A1 of a blank sheet.

Its about to go on 11pm here in Sydney

1614685835207.png
 

rickf19

New Member
Joined
Aug 30, 2019
Messages
49
Hi

worked a treat
Thanks

Copied over relevant columns to sorted Tab in spreadsheet, Filtered out blanks/0's , Pivot table did the rest , just need to get rid of blank lines in Pivot now (can do that by copy paste values sorted Tab ) So should be good to go each quarter when we get that report.

Thanks again

Rick
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
584
Office Version
  1. 365
Platform
  1. Windows
Sounds good, if you include the Yes/N field in the pivot and make that the filter = Yes, it should get rid of the blank lines.
Also if you make the whole data sheet a table, select just the output columns as the pivot data range, it should mean that if you have more data next time the pivot will automatically pick up the extra rows.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,787
Messages
5,638,309
Members
417,020
Latest member
MSVII

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top