Re-ordering Columns From Customer Supplied File

Feb 14, 2010

1 - We receive an excel file from our customer (approx 20 different customers in total)

2 - I have an excel template we have to use, which will import into our MIS software. This excel template must remain the same or it wont import into our system. ie (Columns must be in this particular order)

3 - Our customer sends their own spreadsheets in their own formats ie, column in a different order to our excel template.

4 - I need to know if there is a quick way to remap customer columns into my excel template

5 - Im looking for a little application that will look at the customer spreadsheet and our template in the same window and let me link which fields need to map where in my template. When I have completed I can save what ive done and run when needed for this customer.

View 9 Replies


ADVERTISEMENT

Save File With Cell Supplied Name

Mar 10, 2009

I have a file where cell A1 is the name of the file along with B1 and C1 which provide the versions and variations:-

A1 - Sales Analysis
B1 - Version 2
C1 - Variation C

Is it possible to have a macro driven off a button button which will save the file as (current path to current folder) and then Sales Analysis Version 2 Variation C

View 8 Replies View Related

Re-ordering Some Columns (groups Of Columns)

Nov 22, 2008

I must put in descendent order some columns, but the thing is a bit more complicated...
As my english is not very good i am gonna explain it with a simple example:

----------------------------------
1 4 2 5 rabit fox dog cat
2 6 1 3 bird fis wolf mouse
----------------------------------------

Ok, i must order the numbers in a ascendent way, but reordering one number means also reordering its correspondient name (the name of the animals at its right).
So, again, with an example you will see it better:

Ordering the first row it must appear like this:

----------------------------------------------
1 2 4 5 rabit dog fox cat
----------------------------------------------

I didnt know if i had to post it here or in other subforum and either if it can be done with excel.

View 11 Replies View Related

Dropdown List For Customer In Job File

Apr 25, 2014

I have a workbook that I open for each job we receive. On the first sheet I call the job information sheet I hand enter the following info

Customer name in one merged cell
address in one merged cell
city and state zip in one merged cell

This info is then transferred to a few other sheets within the workbook IE invoice sheet, Labor sheet ect

I would like to created a customer list that I can use a pull down list and input this info on the job information sheet

I have attached a copy of my file for reference.

Master CoopyALL SEASONS MATERIAL & LABOR REPORT.xlsm‎

View 4 Replies View Related

Converting Uneven Rows Of Data Per Customer Into 1 Row Of Data Per Customer

Aug 1, 2014

I have a list of Spid numbers and a service category identifying if it is a Water Spid or a Waste spid.

The Spid is split into 2 parts. The Core is the identifier and the Category states if it is a Water or a Waste spid.

If the 1st digit in the Category is a 1 = Water.

If the 1st digit in the Category is a 2 = Waste.

There may only be a water spid or a property may have a water and waste spid.

What I want to do is have the information on 1 row rather than 2 rows

I have data in this format

SPID
Core
Category
Service Category

100000000151
100000000
151
1

[code].....

and I need to convert it into this format.

Core spid
Water
Waste

100000020
100000020101
100000020251

I have tried IF(lookup) and it works for the 1st row but I can't get it to look at the 2nd row.My data consists of 400,000 plus rows

View 3 Replies View Related

Rearrange Data Supplied By Others

Aug 28, 2009

I have been supplied a list of data (company name, address, tel no, email ect.) unfortunatly the data is not in the correct order. There is about 300 companies on the sheet.

Is there a way i can rearrange the data, i have attached a file to show the end result i am trying to acheive.

View 10 Replies View Related

User Input From A Supplied List

Jun 18, 2007

I want to present a user with a list that they can make a selection from and enter their selection in a cell. Sounds easy but.........

View 9 Replies View Related

Copying Name Supplied In Userform To New Sheet

May 15, 2009

Hello all. Can someone please help me save rows to the variable name stored in dtselect4 (which captures the name input by the user on a userform)?

The sheet is created here: ...

View 6 Replies View Related

VBA - Generate Date Range Within 2 Supplied Dates

Apr 17, 2009

First I would like to thank you for taking the time to assist me. My issue is a follows: I want to have 2 dates (beginning and ending) input on one sheet. With these 2 dates entered, I want to generate a date range between them but have it output on a different worksheet. Here is the function that first pulls the date values entered:

View 2 Replies View Related

Wrong Data Type Supplied To UDF Error

Aug 31, 2007

My colleague and I are writing a udf which uses a "match/ index" combination to return a value to a cell from a named range in another sheet. The cell returns returns the error "value used in the formula is of the wrong data type". When we break down the code into a function which refers directly to the relevant cells, and a sub which returns the value in a message box, it works correctly: here's the code for the function and test sub:

Function MRL(PropertyType, ReturnType, Location, Year2) ....

View 7 Replies View Related

VBA - Generate Date Range Within 2 Supplied Dates (Modified)

May 11, 2009

I just wanted to understand something about some code that was supplied to me on this forum. The code works perfectly, with the exception of one minor issue. The original thread and solution presented is found here

View 2 Replies View Related

Ordering Worksheets By Name

Jun 1, 2009

I have about 45 worksheets in one file. Then all have been named. Is there any quick way to order them? I would prefer not to move each one.

View 2 Replies View Related

Re-ordering Rows

Jul 12, 2009

what i want to do is have a formula set up that works out who has the best position and then re order the number rows to put the player in position 1 at the top. so just moving cells c-n

View 6 Replies View Related

Ordering Of Worksheets

Sep 4, 2009

I need to ensure that a worksheet is ordered after another in a workbook. The macro that this code is in opens a selected file and carries out various operatins on it, so although I can do this fine when the worksheet needs to be created, there are scenarios where it already exists and I simply need to change the order. The sheet that must come in position 1 does not have a common name and so I cannot reference it via it's name in the code. The other sheet is named when it is created so it doesn't necesarilly have to be in position 2, it's just that the other one needs to be in position 1.

View 7 Replies View Related

Ranking, Ordering

Jan 29, 2010

A number of columns (B to U) contain names, details and scores of players. The A column contains the ranking, e.g. 1 to 100.

If a player (row) is deleted, that ranking number is also gone. Is there a way to make the A column always maintain the integrity and completeness of the numbering, e.g. if rank (row) 16 is deleted, the range of B17:U100 moves one row up and the former 17 is now 16? And if a new row is inserted, the ranking numbers after the insertion move up one row and a new number is added at the end?

2. There are 6 columns of scores, P to U. But out of these 6 scores, only the best 4 count for the ranking. The two worst (highest) scores are dropped. Is there a way to automate the process by selecting only the 4 best (lowest) numbers to be summed into the Total column (C), which in turn determines the rankings in the A column?*

And then can the whole range B2:U100 automatically be sorted according to the ranking in column (A)?

*It is possible that there are 3 or 4 bad scores, all with the same value. In that case only 2 could be discarded, and 1 or 2 would be summed into the ranking cell.

View 8 Replies View Related

Lookup Without Ordering

Mar 9, 2008

How do you do a lookup without having to have a list in ascending order?

View 9 Replies View Related

RE-ORDERING A Table

May 1, 2008

i have a 5 by 10 table (5 columns 10 rows)
values only equal 1 or blank
i want to re-order this table in another identical table so that no row will have more than one value (each row will have a maximum of one "1")

currently, the table looks something like this:

blank blank blank 1 1
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank blank
blank blank blank blank blank

what i would like the idential table to show is:

blank blank blank 1 blank
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank blank

View 9 Replies View Related

Ordering A Column By Date

May 30, 2013

I have 320 rows of data, of which the date is in the first column. I need the data sorted into date order, ie. 04.05.12-->30.04.13 and all the dates in-between (note these are British laid out dates). I am aware, and have already done Format Cells --> Date --> 14.03.01, as this is how my dates are laid out.

I've tried sorting this column, ascending, descending etc etc etc, obviously it needs to be 'expanded' to ensure that the rest of the data in the row follows it to its new position. No joy. It only ever sorts the dates by the day ie. 02.04.13, 04.12.12, 06.06.12. Doing it by the day and not the month and the year also.

I don't want to order 320 data rows myself!

View 3 Replies View Related

Ordering If, Else, And ElseIf Statements In VBA

Jun 6, 2009

I have an enquiry where I'm trying to order error messages which occurs in if, ElseIf and Else Statements. The code I have works. However, I would like the error messages to be checked in a particular order. I tried rearranging the conditional statements but had no success. Here is my

View 5 Replies View Related

Ordering Of Shortest Price

Sep 28, 2008

I want to have them in order of shortest price (favourite) first with the relevant horse's name in the adjacent column. I have just discovered vlookup which seemed perfect - until I discovered that when there are two or more identical prices it only returns the 1st horses name.

Is there any way I can use vlookup to do this (with more than one identical value)

View 9 Replies View Related

Combining Strings Of Numbers And Ordering Them

Jan 26, 2007

I am trying to come up with a formula which would combine two groups of numbers, put them in numberial order and remove duplicate numbers:

3 6 10 22
1 3 5 9 10 13 19

The above two groups of numbers are in separate cells in two different rows.

the result would also be in a different row: 1 3 5 6 9 10 13 19 22

View 14 Replies View Related

Production Schedule And Supply Ordering?

Jun 7, 2013

I need to make a spreadsheet on excel which shows the production schedule of a product over 4 days.

Then I need to make a supply ordering sheet which shows how much supplies are needed to do the above task and how much each item in the supply list will cost and when the items would need to be ordered.

View 4 Replies View Related

Setting Up Stock Ordering System Using Excel

Jul 15, 2013

setting up a stock ordering system in Excel. I have been sent a spreadsheet with about 400 items I've ordered before and about 15 columns of descriptions which include the name, category of item, manufacturer, cost, number etc.

I would like to set up a simple Excel spreadsheet which staff can use when they want to order some stock, which will automatically fill in the adjacent cells with the product category and unit cost, once the name has been typed into the first cell. Once I add the SUM formula I can therefore have an immediate total of how much each staff member is spending on stock that week, without having to look at the invoice I get back from the supplier.

I know Excel has a predictive text style feature (autocomplete?) but I don't know how to get it to automatically fill several cells in a row with data that is linked to the first cell.

View 14 Replies View Related

Ordering List Whilst Ignoring Zeros

Oct 21, 2013

I would like to reorder a list whilst ignoring any 0 values. I've attached (I hope correctly!) a spreadsheet of what I mean. It's pretty simple, but I've been banging my head over it for a while now!

Sample.xlsx

View 4 Replies View Related

VBA - Checking For Blank Cells And Ordering Numbers

Feb 9, 2007

I have the following macro:-

ActiveSheet.Select
ActiveSheet.Copy
Columns("A:D").EntireColumn.AutoFit

Range("A2:B500").Select
Selection.Sort Key1:=Range("B2:B500"), order1:=xlAscending, Key2:=Range("A6:A250"), order2:=xlAscending

Within this I would like to add a couple of features which check:-

1) If there is a number in column A - there should be one in column B
2) The numbers in column B are sequential i.e. 1, 2, 3 etc without any gaps in the sequence

View 9 Replies View Related

Locate CSV File With Open File Msgbox And Import Columns From CSV To Worksheet

Oct 28, 2011

Actually i've downloaded Tracking report of my SPO and this report is on 4-5 csv files (1 file per week). I have to gather data from these 4 files into one Worksheet. I have to do this with command button on my worksheet; with Open file dialogue box (i want to locate the csv files).

CSV files are something like that (I need only first 4 columns):

ABCDE1Name MSISDN Date Location MapLink 2M. Younus Safi "923***550577" "2011-10-07 20:36:18" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 3M. Younus Safi "923***550577" "2011-10-07 19:36:26" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 4M. Younus Safi "923***550577" "2011-10-07 16:39:58" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 5M. Younus Safi "923***550577" "2011-10-07 15:37:23" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 6M. Younus Safi "923***550577" "2011-10-07 14:37:05" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com"

and my Worksheet is like that (With a command Button):

I want to import first 3 columns of csv file on first 3 columns on my worksheet; leave 2 columns blank and then import the 4th column... now for example i have done importing data from 1st csv file and the data is on 50 rows.. i click the command button again, locate the 2nd csv file.. do the same thing (import 1st 3 columns, 2 blank, then 4th) --from 51st Row-- and so on...

ABCDEF1NameMSISDN DatedBlankBlank Location2

3

4

5

6

7

View 4 Replies View Related

Dependant List - Organize The Ordering Process From Wholesalers?

Dec 21, 2013

I have a list on flower names in column A , and the colors in Column B. For sorting purposes I have duplicates in Column A. I am creating an easier way for coworkers to organize the ordering process from wholesalers. On the order sheet It will have the dropdown that lets them choose the flower (ultimately I hope to make this searchable,) Once the flower is selected the next column will allow them to choose from the color available. Attached Is the sheet with my work so far.

View 8 Replies View Related

Sheet Preparation By Ordering And Swapping Cells Vba Macro

Jun 5, 2007

in 1 workbook i have 2 sheets (sheet1 and sheet2) with same format of data that I need to compare and compile in sheet3 by a primary key column.

example:

sheet 1 & 2 data format:

|Atr1|Key|Atr2|SubAtr1_1|SubKey1|SubAtr1_2|SubAtr2_1|SubKey2|SubAtr2_2|Atr3|
..................... ......................................../......................................../
......................................Set1.....................................Set2

To be more specific I can compare this with an electrical wiring table, where:

Key is the wire number
Atr1, Atr2, Atr3 are general atributes of the wire number
Set1 are the atributes of the first end of the wire, called "from":
SubKey1 is the "from" device and its subatributes SubAtr1_1 & SubAtr1_2
Similar for the other end of the wire is Set2.
This means that Set1 and Set 2 have similar format & type of data.

I have found on your site an example of macro that is doing the comparision between the 2 sheets of data but I encountered a problem...sometimes on sheet2 Set1 and Set2 are reversed and are highlighted as a difference but I don't want this.
Please help me with macro that would create a rearranged copy of sheet 2 respecting the following conditions:

1. if Key from sheet1 cannot be found on sheet2 then copy entire row on sheet3.
2. if Key from sheet2 cannot be found on sheet1 then copy entire row on sheet3.
3. If Key from sheet1 = Key from sheet2
If SubKey1 from sheet1 = SubKey2 from sheet2
& SubKey2 from sheet1 = SubKey1 from sheet2
Then reverse Set1 with Set2 by swapping cells
and copy entire row (with Set1 and Set2 reversed)to sheet3.

All data on sheet 3 should be sorted by Key.

View 7 Replies View Related

Moving Columns Of Data Into Another File And Saving File With New Filename?

Aug 30, 2013

I was wondering if there is a way to write a macro that will take an excel file that I have called Alldata and then have it copy the first two sheets to a new workbook named something else( FullSparameter_0) and then copy the name of the third and fourth sheet into the new FullSparameter_0 workbook and then copy column A-E from sheet dB Alldata.xls and paste it into the FullSparameter_0.xls workbook in the same dB sheet in the same spot. And do the same thing with the Phase Sheet. i know this sounds confusing, but im really new to the VBA stuff.

I just need 15 different files from the original Alldata.xls that will be named FullSparameter_0, FullSparameter_1, .., FullSparameter_14. but each file will have the same two first sheets, Setup Information and Cal Verification, and the same last two sheet names, dB and Phase, but then in the dB and phase sheets, it will copy over the next four columns of data. so in FullSparameter_0 it will have column A-E from sheet dB in Alldata.xls in the dB sheet in FullSparameter_0 (Same with the Phase Sheets). Then in FullSparameter_1 it will have column A and Columns F,G,H and I (for dB and Phase). In fullSparameter_2 it will have column A and then J,K,L and M. Each FullSparameter_X will have the exact same first two sheets and then the other columns for the last two sheets will be pasted into columns A-E in dB and Phase.

View 1 Replies View Related

Excel 2007 :: Date Filtering - Change Date Format Supplied By Date Picker

Apr 24, 2012

I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.

I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).

When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)

So my questions are:

1. Is this due to the formatting?

2. Is there a way to change the date format supplied by the date picker?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved