experts dealing with huge data needed please

I

Invader

New Member
Joined
Jan 22, 2013
Messages
14
Hi all,

i have 1 year sales data which exceeds 5 million rows, my issues are as follows

- each row is in a different currency which i need to convert them all based on monthly rate (i have a monthly rate sheet)
- i need to combine them all and use 1 pivot table
- the data is heavy please help
- i need a column for total tax which is : the sum of (Ads Taxa1, Ads Taxa2, Ads Taxa3) without the alphabetical codes and i use "Ads Totl" currency code (which is the first 3 letters of the "Ads Totl" cell)
- "Ads Fare" cell have their own currency which i separate by taking the first three letter of the cell, "Ads Totl" cell i do the same.

"below is few rows example of the data that i have"

please help me urgently

Bas Pnrr PnrBas Agtn NoTicket_NumberAds FareAds Taxa 1Ads Taxa 2Ads Taxa 3Ads TotlItn Clsc 1Itn Clsc 2Bas Dais DtAds TourAds Orin
EVLRQO86210409181414188BHD 38.000 5ZR 50BH 10HMAED 445N05-Oct-16
FEIPDJ86209979181407103BHD 38.000 5ZR 50BH 10HMAED 445N05-Oct-16
JHZRWV86210409181414191BHD 38.000 5ZR 50BH 10HMAED 445N05-Oct-16
XTJDNB86490292428450116AED 500 40DV 100YQ 180XTAED 820NN05-Oct-16
XTJDNB86490292428450117AED 500 40DV 100YQ 180XTAED 820NN05-Oct-16
JTBJNO86490292428450108AED 510 40DV 400YQ 280XTAED 1230WW05-Oct-16IT0116DXBCPCGF1

<colgroup><col span="2"><col><col><col span="3"><col><col span="3"><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
What columns of data do you want ( it sounds like Ticket Number can be ignored?)


how do you want it consolidated? i.e Rolled up into Month Currency entires or how

Date, Curr, Total value, TotalTax
OCT 2016, BHD, 309, 195


Need to know How you want it to look
 
Last edited:
Upvote 0
Hi, thanks for your reply

i would like to add few columns to the raw data

- to convert all currencies to BHD
- add three more columns to show look up value based on Bas Agnt No

Fare BHDTax BHDTotal BHDMonthYearPOS City CodePOS Agent NameCategory Code
###.######.######.###Jan2016

<tbody>
</tbody>


than in the end i want to have two tables one to show the agents sales by month

something like the below

CityCategory CodeJanFebMarAprMayJunGrand Total
WGFArea Office
WGFEB2 BOOKING ENGINE - BAH3,245,7292,890,8953,918,4243,696,3033,423,5113,643,39320,818,255
WGFBACKUP ONLINE SALES - BAH10,1644,6408,7769,5718,0776,44447,671
WGF Total3,255,8932,895,5343,927,2003,705,8743,431,5873,649,83720,865,926
BAHArea Office
BAHGULF AIR SALES OFFICE718,283555,603643,944807,4792,470,167761,7745,957,250
BAHSTAFF TRAVEL SALES OFFICE119,06188,450145,989138,796135,659123,687751,642
BAHHDQ - MYID TRAVEL22,84221,68228,56026,82320,69419,985140,587
BAHSTNME-STAFF TRAVEL1,1236381,761
BAHWEB B2B BH1122562815261,175
BAHBAH-BAHRAIN-SEEF (SQMS HDQ QUOTA)1818
BAHAirport Office
BAHGULF AIR SALES OFFICE172,146121,872158,696156,991166,641173,092949,438
BAH Total1,033,455788,376977,4451,130,0892,793,4421,079,0647,801,871

<colgroup><col><col><col><col span="5"><col></colgroup><tbody>
</tbody>


lookup will be taken from a table like the below

POS Agent CodePOS City CodePOS Agent NameCategory CodePOS Country Code
8621320AANAKBAR TRAVELS OF INDIA GULF LLBSPAE
8624424AANAL AIN EXPRESS TVLBSPAE
8620013AANAL AMAAN TVL & TRSMBSPAE
8621498AANAL DAAR TRAVELBSPAE
8621151AANAL-ETIHAD TRAVEL AND TOURISMBSPAE
8621353AANALHADAF TRAVEL L.L.CBSPAE

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>



and yes ticket number column is important to remove duplicates and to have a count of tickets sold

waiting for your reply
thanks
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,889
Messages
6,122,097
Members
449,065
Latest member
albertocarrillom

Share this page

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
Back
Top