I have hundreds of .dbf files that I am manually converting to .csv using Excel. Unfortunately, my .dbf collection is growing faster than I can convert them. Is there a way to convert all the .dbf files in the current folder to .csv? All the .dbf files are in the same folder.
The path to them would be the same as the macro file.
ive got table consisting of about 4000 entries. Below is a small example.
[url]
I want to be able to make a button through macro's, where when pressed, it shows only certain postcodes - the way i've been doing it so far is using a combination of If, And, and Or functions to return true or false if a postcode begins with either 'AH4, AH1' and has an order number beginning with 'W', for example. Then I'd record a macro where it shows the ones showing true, via data filter. The problem I've got is I need to search for about 50 different combinations of postcode, out of a list of maybe 300 types of postcode, and using my current IF,AND,OR functions, I simply run out of room in the formula bar! For example, I need find a postcode beginning with CB1-25 (i.e. CB1, CB2, CB3) etc..etc.. the only problem is if i use my current LEFT function, and i want to search for say, CB1, CB14, CB15, out of a list of 'CB1,CB14,CB15,CB16', it will still return a postcode beginning with CB16, due to the CB1.
In the link I've highlighted the postcodes I want to search for out of that small list, and hopefully any solution I'm giving here I could expand to up to 100 combinations out of 500 combinations of postcode, for example.
How do I get it to search for a massive combination of criteria?
I have hundreds of numbers in column A and I want to combined these numbers in call B1 with “ , ”
e.g.
column "A"
451
45
61
48
194
Call "B1"
451,45,61,48,194
I am using attached sheet for this thing but I can’t use any other new sheet, is there any another way to do this. I need formula. So I can use any sheets.
I am tasked with making a matrix that I cant seem to solve.
I have solved it on a small scale using
=SUMPRODUCT($E$5:$E$19,--ISNUMBER(SEARCH(O11,$C$5:$C$19)),--ISNUMBER(MATCH($D$5:$D$19,{"One","Two","Three"},0)))
The issue is that I need where {"One","Two","Three"} is to contain between 3 and 334 conditions.
Basically we have a data table that contains "channels" that have multiple affiliates under it. Then we have a data table by affiliate by country and revenue... I want revenue by Country By Channel...
I have this value in a cell 38761.8296804781 which is displaying as 38,762. I want to display it as $38,761,829. Is this possible without going through each cell and moving the decimal point?
View 3 Replies View RelatedI have over 500 countries with unique shipping costs associated with each... each of them has the same mark-up price... 2.25. I am hoping to be able to insert a formula that will cover each country... i am currently entering each one indivdually (E10*2.25), then the next one (E11*2.25), etc.
View 5 Replies View RelatedI have a very large spreadsheet that included a large number of SUMPRODUCT formulas. Many of them are running formulas that have SUMPRODUCT layered.
I have most of the SUMPRODUCT lines running 2 arrays to check for the date field. It sees if the date is >= the start date cell and <= the end date cell.
It runs fine, and works great, from a data standpoint. However, I am already running into slowdown when altering the date fields. The workbook is still in the starting stages and is going to get MUCH larger.
I have attached a sample sheet showing a little bit of what I am talking about. The SUMPRODUCT functions on sheet 3 all point to the start and end date fields, and the data updates as they are changed.
I need to find a way to condense the SUMPRODUCT lines. Is there a way that I could run one SUMPRODUCT line in a cell and then reference that cell for all of the other lines? Or is this just the best way to do this?
Streamline.xlsx
I have a some data where I need to sum some data based on date range, and dept id's. But the dept ids are parts of various groups. For ex, one group covers about 300+ dept id's. Some of them have ranges (e.g. dept 1000 - 1050) but for whatever reason some dept id's that belong to other groups sometimes fall into the same ranges.
My first hope was one big range, but the overlap issue nixed that. As I mentioned there are some ranges so maybe 100 of them could be grouped into a range but I'd still have 200+ independent dept id's.
I read for 2003 the max criteria for sumproduct is 30 and for 2007+ it's 255. So maybe 2007 might cut it but this doesn't seem like the right way.
Btw, I'm doing this in VBA but I mention sumproduct because I've used it a few times within code via evaluate. Otherwise do I just have a massive string in vba (If deptid = #### or deptid = #### or deptid = #### etc)?
Again, it seems like there should be a better way. I thought about making a master list of all the dept's and sumproduct each one individually and then sum from the ones that fall under group but even then I have to identify the deptids I need for a particular group.
I am using Excel 2010
I have over 800+ pages of chart that only takes up 6 columns and around like 9000+ rows.
I wanted to print this chart on paper and need hardcopies. However, the chart in its current setup prints only on the left half of the page leaving the right half empty.
How do i make use of the full space properly? Each chart has a "page number" on it so I want the chart to print continuously from one half of the page onto the next half and then the second page, third, etc.
Here is a visual demonstration of how things currently are and how i'd like to get them to be:
As you can see, This is the first of many charts and its numbered Page 9 and next one is page 10.
Pic1
How this looks when i try to print, it's only on the left side. right is all blank
Pic2
How i want it to look like upon printing
Pic3
As you can see in the last picture, once page 14 chart has no space it automatically continues chart on right side of page and then moves on to print rest.
[URL]
I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.
The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.
I've taken on an old excel datasource any I need to restructure it and a column is foxing me.
The column lists time in mm.ss format (decimal seperator), so 1.5 hours is shown as 90.00. For future use, I need this column to be formatted in hh:mm:ss (colon sepertors), ie 1:30:00
If I simply change the cell format to hh:mm:ss,
I am trying to save a file from xls to csv. One of my columns in the xls file is filled with numbers separated by commas but is saves as a text. Whenever I save the file as a csv it converts the numbers saved as a text to be saved as numbers so it screws my commas up and therefor messes up the entire file.
View 10 Replies View RelatedI am using the function =LOOKUP(J2,Sheet4!$A$2:$A$1000,Sheet4!$B$2:$B$1000)
which sometimes returns a #N/A error. I want it to return an empty cell instead in those cases. My attempt was =IF(LOOKUP(J2,Sheet4!$A$2:$A$1000,Sheet4!$B$2:$B$1000)=NA(),0,LOOKUP(J2,Sheet4!$A$2:$A$1000,Sheet4!$ B$2:$B$1000)) however, it still returns a #N/A error. Is there any way around this?
I have a rather large "application" that I've built into a spreadsheet at work (about 1200 lines of code, 4 forms, and code on a few spreadsheets).
I'd like to convert all this code and the forms into a VB6 project so I can have this application as an executable separate from the workbook, if possible.
I tried exporting the forms, but they show in the project explorer as Designers.
how can I creat a formula say to convert a time (mm:ss) to seconds (ss) only. Is that possible? And how do I do it.
I got a column say of these values and I want whats after "="...
3:07 = 187 sec
1:10 = 70
5:16 = 316
1:25 = 85
5:40 = 340
I have 2 weeks of data that I need to convert the Average handle time to seconds only. All the data is in 30min invertals for a full 24hr day.
A1 = USD$100, I want C1 = 100, What is the formula for C1,
View 9 Replies View RelatedDoes anyone have a simply way to convert UST time to EST.
Keeping in mind that we use military time format.
When coding my macro to stored to .csv file I encounter a problem.
Buildup file:
Row A: has 24 columns with header info
Row B: has 15 columns with header info
Row C: has 9 columns with header info
Row D: has 24 columns with record info
Row E: has 15 columns with record info
Row F: has 9 columns with record info
Row G: has 24 columns with record info
Row H: has 15 columns with record info
Row I: has 9 columns with record info
etc...........
I have times in 24h clock format and want to change to 12h so 18:00 will look like 06:00 see attached for actual formats
View 10 Replies View RelatedI am trying to convert a row with cells referring to other cells (or with formulas in them) into columns, but paste special - transpose does not do the trick; the reference somehow changes when I try to paste it in, say A5 in the example file. I have excel for mac.
View 2 Replies View Relatedi try to convert several decimal values into Days Hours:Minutes:Seconds my problem is the following. If i user the format DD hh:mm:ss values over 32 will start counting the days from 0 (like a new month). and if i format it like M D h:m:s values < 32 days gets displayed with a 1 (for the month) before the days.
[Code] ......
is there a way to get only the count of days displayed without the summarization to months?
I am trying to convert cells so that I can pull data into a pivot to only show month & year. I have connverted the format in the cells to custom mmm-yy in the source data w/sheet which displays it how I want it to but the pivot table also picks up the day so instead of having groupings by month/yr I am getting groupings by day/month/year.
View 2 Replies View RelatedHow to convert the excel formula into its value? I know the easiest way is to copy and use paste special then "value" options but is there a way to make it automated in case the desired output value has resulted.
HTML example:
A1 B1 C1 D1
1 2 A1+B1 Yes/No
on the above example, if D1=Yes, the output C1 will become value equals to 3 and the formula will be remove, if D1=No, the formula will still remain.
I have a lon list of dates in the format dd/mm/yyyy. I can change the format to DDD to show the day of the week. What I want to do though is make that permanent, not just in the format.
View 4 Replies View RelatedWhat is the Easiest way to Convert this Date and time format into Dates Format i.e MM/DD/YY
View 5 Replies View RelatedHow do I get a straight conversion from h:mm to h:tenths? As soon as it hits 23:59, the clock start over in the tenths column. ie...39:27 should read 39.4. I'm currently using this formula: =(T10-INT(T10))*24
3:283.5
8:508.8
9:269.4
10:4210.7
12:5712.9
14:1214.2
39:2715.4
41:4217.7
59:5711.9
how to convert .xlsx file into .csv?
View 4 Replies View RelatedI have a list of date and want to convert those date to day name.
1-Jan-10
2-Jan-10
3-Jan-10
4-Jan-10
5-Jan-10
6-Jan-10
7-Jan-10
8-Jan-10
9-Jan-10
10-Jan-10
11-Jan-10
12-Jan-10
13-Jan-10
My question is i have use simple sum function and want to total of sum show in text another range as like 1400 Rs FOURTEEN HUNDRED ONLY
View 1 Replies View RelatedIs there a way to convert a csv file to an xls file without using any software.
View 4 Replies View Related