The FOB PRICE in sheet JAN09SEA is in any of three currencies, HKD EUR or USD.
The output on sheet CONSOLIDATE should only be USD.
Also, you can see that some countries send their freight in two or even three different currencies. This would need to be accounted for.
Additionally to the currency issue, I need to sort the output to RM or FG. For that, on sheet CONSOLIDATE, we have a button located in G1 to switch types.
E.G. if presses for FG, the formula should only check for the values where the row is marked with FG in column X.
The freight charge is always in HKD and needs to be converted to USD as well. Also it should be FREIGHT = FREIGHT CHARGE + SURCHARGE converted to USD.
This is quite a lot of information. I hope someone can think of a solution for the problems.
If you need more information.
i have an order book which lists the value of each order against the currency it was ordered in. So in column A i have a list of currencies (THB, GBP, AUD, USD, the user clicks the appropriate one) and in column B i have the figure (23.50). I want a formula that can convert all these in GBP so that i can report the order totals in one currency in column C. =(IF A1="THB", B1/50, "") can i adapt this to include all variables? I will add a table with the currency conversion rates.
I have a Userform where i have the user in put a dollar amount in a text box. Im having difficulty getting this assigned to a variable as an integer. I get a "type mismatch" error. I have tried declaring the variable as an integer, and tried the cint() function, but i get the same error.
How to make EXCEL 2007 Automatically convert all my CURRENCY symbols in all cells formatted for CURRENCY in THE SAME SHEET to the one and ONLY currency selected in JUST ONE cell ? (i mean, after conversion, all of them are Not just display currency symbols, but Calculable monetary values like regular Excel's built-in currency format)
I am a dummy in VBA so I really am not familiar at all if the solution is really one of VBA. I don't even know how to start programming VBA at all in Excel.
I have 2 separate pivots running off 2 separate data sources. The filters used for each or fairly similar (bar one entry).
Is there a way to have one filter that filters the information in both pivots even though they are not based on the same data source?
Also, with the filter that is slightly different; in one table, I have an option that shows (this is just an example) 'retail and end user' yet, in the other table these are two separate options.
I have 1000's of rows of data and 8 of the columns are reserved to tag possible defects. All 8 columns pull from the same drop down list of all possible defects. I have 8 columns set aside because a row may contain multiple defects.
What I want to do is look up a single defect (ie "scratches") within all 8 columns. See my attached simplified example. If I filter column B for scratches, the scratched part in row 4 will be ignored. Can I apply a filter across multiple columns?
I have a spreadsheet containing 5 columns of text, I need to identify any that contain any of the following characters;
` ~ ! @ # $ % ^ & * [ ] | } { " _ ; < >
Is it not possible to do this via either a custom "contains" filter or Find/Replace without doing it one by one ?
This is something i will need to do monthly as a data cleanse activity, and ideally i need it to be quick and allow me to see quickly out of a large list which contain these characters in the form of a filter or highlighted cells.
My excel knowledge is limited but if this isn't possible via custom filters, how i can create a macro that will search for the above characters and highlight any cells containing them ?
I am looking for a way to combine three pivot tables in to one. Basically, one sales rep will have data for the current month, the year to date, and the total year. I have to compile this data to one sheet, for 30 sales managers! The problem with running the pivot three times (once for monthly, year to date, and yearly) is that brands that aren't sold in the month are then left off the table, which leads to a mess that it takes far too long to clean up. I hope I've made this clear, but please check the attachments for a better idea of what I'm looking to do...
I have a Excel sheet which has a Product type values ASP,VSP etc in the first column. The second column has values Product Sub Types,which has values abc123, abc123456,abc123789, def123,def123456,def123789.The third column ServId's abc01,abc02,def01,def02.
If the Product Type is ASP,then I need values abc123,def123
But if Product Type is VSP, then I need values abc123 abc123456;abc123789 def123 def123456;def123789
I have a dataset, which I would like to sort on multiple criteria. The code I have is :
Dim Lst As Long Dim Hdr As Range
Lst = Range("A65536").End(xlUp).Row Set Hdr = Range("A6:AD" & Lst)
With Hdr .AutoFilter .AutoFilter Field:=9, Criteria1:="Complete" 'Match Status .AutoFilter Field:=14, Criteria1:=Array ("Pending", "Technical", "PR", "Regional", "=") 'Case Study Status End With
Field 9 Match Status will always be Criteria "Complete" Field 14 may contain different variables depending on the dataset. I want the filter to bring back all Match Statuses (Field 9) "Complete" and multiple Case Study Statuses in Field 14.
This code filters correctly on Field 9, but is only bringing back blank cells in Field 9 when there is data that has Case Study Status "Pending", "Regional" and "Blank".
I am trying to run a macro to filter data based on 3 criteria. i want to filter column c to show rows with "Application", "Application / Web", and "Application / Database" which is referenced in the criteria range stated below.... Sheets(" Validation").Range("D95:D97"). for some reason it does'nt pick up the answers when i know that they exist in column c. could it be that they are formulas populating column c?
Sub Macro3() Range("c4:c200").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Sheets("Validation").Range("D95:D97") End Sub
In the attached file, I created an example of a list I’m trying to break down and combine data from multiple cells into one cell. As it shows on sheet1, level 1 has two people who occupy it, Paul and Peter. I am trying to get the names Paul and Peter to be combined in one cell next to the #1 as shown on sheet2. I want it to put the correct amount of people next to their corresponding number as shown on sheet2. Is there anything that I can use that may make my life a little simpler? This will be done on a weekly basis, and incorporates a couple hundred items.
I am work a worksheet that contain the data like that A B NUM1 Jene,Joly NUM2 Jene,Selo,Diff MUM3 Tino,Selo ……………… this worksheet has over 1,000 data. i want get the result in sheet2 as below: A B NUM1 Jene NUM1 Joly NUM2 Jene ……………… i write the code [PHP]Sub test() Dim i As Integer Dim arr() As String Application. ScreenUpdating = False On Error Resume Next For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row arr = Split(Cells(i,2), ",") For J = 0 To UBound(arr) With Worksheets(2) LASTROW = .Cells(Rows.Count, 2).End(xlUp).Row + 1 .Cells(LASTROW, 1) = Cells(i, 1) .Cells(LASTROW, 2) = arr(J) End With Next J Next i Erase arr Application.ScreenUpdating = True End Sub [/PHP]
the defult of this code is that it will take long time to get the result.so,i want to know how to make the code run fast.
I have a huge excel file. This file contains Projects, Project Manager, departments and sales for different quarters. Each department has sales and the quarter the sale was done. Now I want to create a pivot table where I can see the sales for each quarter for each project or each project manager. Attached file may elaborate the problem. ShaA1.xlsx
I am staffing an upcoming event with volunteers and I want to be able to a single volunteer across their various shifts and locations. However, I cannot guarantee that a particular volunteer will always be in the same column on my spreadsheet so AutoFilter doesn't do the trick. I've tried using Advanced Filter options as well, but I either can't seem to get it to work or it won't work with strings.
I've attached a sample of the type of document I'm working in (genericized) so hopefully my below questions will make more sense.
Ideally, I'd like to be able to be able to search/filter for Joe and see rows 2 and 3 appear. Is this possible?
Additionally, I'd like to know who is also on shift together. For example, is there a way to search for Tony and Sarah together and have row 5 appear. I know that I can do this now with filtering column 2 and then 3, but the real document I'm using is over 800 rows long (very big event!) and using progressive filtering would take longer than I'd like.
Is there a way to filter data based on multiple OR condition with a like parameter.
I have data which has near about 50000 rows and now i would like to filter on a header called "Activity Type" and would like to fetch result for a text which resembles "from ABC group" or "From PQR group" or "From XYZ group".
Well we are trying to fetch some data using excel filters. But we are unable to apply filter simultaneously on two excel columns. The problem is explained in the attached sheet. If you have any query/doubt in understanding the whole problem then writ it.
i have my "raw data" and in my "summary" sheet, i want to show the data by the data that is filter in range D15:D21
Essentially, when the user filters the data from cell D15, i want the the macro to paste the data row D27 down/across from the "raw data" table. in addition, i am not sure how to create dependent drop down.
for example, in cell D15 it shows "segment"; in the cell below, D16 (tyre size), i want it to list all tyre sizes within that selected "segment"; when i want D17, to show "speed index" for by segment and tyre size etc.
in the table, i want the macro to show the data based on what the user filters from above. i can then do my calculations but getting this to work is the tricky part.
Can I use vba advancedfilter to work with more than one criteria?
I presently have one range designated. At the top cell has the field, or column, header name being "Student", then followed by a list of 6 people, located in Sheets("Extract").Range("A1:A7"), which is then extracted from Sheets("Complete").Range("tblPrimary[#ALL]") to Sheets("Extract").Range("AA1") as in:
[Code] ......
I want to also be able to filter out a specific month, whose field/column name just happens to be "Month", but I suspect I will need to change it to "InfoMonth" or the like to avoid the probable key word of "Month"...
The months are numerical in those fields - 1 through 12.
Can I add to the present filtering line or do I need to then create an additional filter?
I have a range of data (up to 20,000 cells) that has up to 30 different entries. I need to apply a filter to this data to filter out all but 5 of these.
I have a list of farmers in A1:A1000 with the types of livestock they keep in col B, delimited with commas and spaces e.g:
Col A Col B Name Animals Smith Cows, Pigs, Sheep, Horses Jones Sheep, Pigs, Chickens, Geese, Alpaccas Price Cows, Sheep, Pigs, Chickens, Rabbits Williams Cows, Chickens, Horses, Alpaccas, Pigs
I need to be able to filter this list using up to 3 criteria, e.g. Filter all farmers with Cows, AND Sheep, AND Pigs. Applying this to the data above would show Smith and Price.
The user would need to enter the criteria somewhere, preferably in 3 cells, let's say D1, D2, & D3. I reckon I need to use Advanced Filter, but not sure how to do it with all the animal types to be filtered being in one column.
