# Sort data to enable summary report

#### rickf19

##### New Member
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.

Australian Dollars at CD R4, 6 AND 8
Ticket No at CD R5, 7 AND9
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.

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
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
Hi Alex
cant do xllb as its a work version of 365 and they dont allow addins.
 CUSIP Description User Sec. Type Trade Date Quantity Broker Principal Local Int. B/S Local Net Amt. Local Gain/Loss Local Cancelled Ticket# Coupon Maturity Rating Exch. Rate Settle Date Orig. Face Price Principal GBP Int. B/S GBP Net Amt. GBP Gain/Loss GBP
cols D to P

thanks
Rick

#### Alex Blakenburg

##### Well-known Member
Hi Alex
cant do xllb as its a work version of 365 and they dont allow addins.
 CUSIP Description User Sec. Type Trade Date Quantity Broker Principal Local Int. B/S Local Net Amt. Local Gain/Loss Local Cancelled Ticket# Coupon Maturity Rating Exch. Rate Settle Date Orig. Face Price Principal GBP Int. B/S GBP Net Amt. GBP Gain/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

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
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
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
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
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)
AE6:AQ22AE6=IF(AND(\$P6="Yes",C7<>""),C7,"")

Last edited:

#### rickf19

##### New Member
Thank you for the reply I am going to test it this afternoon
I am in UK so GMT

Thanks very much again very much appreciated

Rick

#### Alex Blakenburg

##### Well-known Member

Thank you for the reply I am going to test it this afternoon
I am in UK so GMT

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

#### rickf19

##### New Member
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
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.

Replies
2
Views
1K
Replies
5
Views
379
Replies
3
Views
340
Replies
0
Views
421
Replies
0
Views
290

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.

### Which adblocker are you using?

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

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