Hard Sorting Process / Dates And Supplier Code Mixed In Same Column?
Jan 22, 2013
I have an auto generated report that is exported into .csv, my dilemma is that it sorts both the account code and the date in the same column, like this:
9884A B Gerrish Ltd Å“ 577.270577.270
30/09/201231/10/20121865518655277.010277.01MAL/553260
30/09/201231/10/20121884218842264.260264.26MAL/553259
30/11/201231/12/2012195541955436036MEL/549200
so "9884" and "30/09/2012" are in the same column.
Some how i need to turn this information to say the below three invoices are for account 9884, into more of a usuable data like:
9884 A B Gerrish Ltd 30/09/2012 etc etc.
Obviously through out the document this repeats for each account, upto some 1000 accounts.
jas.xlsx‎
View 11 Replies
ADVERTISEMENT
Jun 17, 2014
A column holds numbers and numbers with suffixes. The conventional sort function pushes the text values to the bottom. I need them sorted along with the numbers.
Microsoft's KB says to add a column formatted as text and then to RETYPE ALL OF THE VALUES! Splendid. But my file has hundreds of records. Re-typing all of them would be a major pain. On the other hand, if I had started typing while trying to find a way to sort the darn thing I might be done by now.
Here's what I know so far: If we create a column, format it as text and then populate with 1, 2, 3, 4, 1a, 2a, 3a, 4a, etc. it will sort exactly correct (1, 1a, 2, 2a...) after selecting the "sort numbers and numbers stored as text separately" when the Sort Warning appears. If you try to create the sample I typed here, you need to be careful that the area is formatted as text BEFORE entering the text. You apparently cannot change the format of an existing column of data to Text or if you do it does not have the desired effect when sorting. It might be useful to know if this can be done too. MS suggests not.
View 11 Replies
View Related
Mar 28, 2014
I have a worksheet with a row of mixed cells near the top. By "mixed", I mean some cells have only numeric data (numeric-cells) and some have only words (word-cells).
GOAL: I want to sort the columns to the following order:
First all the columns with only numeric-cells in the mixed row, with those columns sorted in DESCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order.
So far all I have been able to accomplish is:
First all the columns with only numeric-cells in the mixed row, with those columns sorted in ASCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order. This was accomplished by a simple sort using ASCENDING order on the mixed row.
OR
First all the columns with only word-cells in the mixed row, with those columns sorted in DESCENDING alphabetic order of the word-cells in the mixed row; followed by the columns with only numeric-cells in the mixed row sorted by DESCENDING numeric order. This was accomplished by a simple sort using DESCENDING order on the mixed row.
This may sound as confusing to you as it does to me, so I have attached an example file showing hypothetical Input Data and the Desired Result.
I did the example file in multiple steps, but that's too painful in the general case where calculations determine what is in which cell in the mixed row. All data are calculated, even the word-cell data AND so dependent on other data, and the columns in the mixed row that are word-cells and those that are numeric cells don't remain the same.
Eventually I'd like to do this with a macro, so if VBA has some power to do this that EXCEL commands don't, then that's fine.
I could probably live with the final result as numeric-cell columns sorted in DESCENDING order followed by word-cell columns sorted in DESCENDING order, but I haven't even been able to accomplish that AND it's not quite the goal.
View 3 Replies
View Related
Jul 17, 2009
I have a list of dates in a column that I need to sort. Dates in columns are as follows for example:
02/15/2010
05/02/2009
06/11/2033
04/05/2044
When I get do a sort I get the following result (it appears to be sorting by month, day, year)
02/15/2010
04/05/2044
05/02/2009
06/11/2033
I want to sort by year, month, day. Desired result as follows:
05/02/2009
02/15/2010
06/11/2033
04/05/2044
View 2 Replies
View Related
Nov 4, 2009
So Column 1 I've got dates, need to sort through that and calculate Year-to-date and Month-to-Date values. These are both Sums of the cells....
YTD = Sum of all cells with most recent yr, in this case 2007
MTD = Sum of all cells in Column B for most recent month, Feb2007 here.
I've listed the desired solution for YTD and MTD on the sheet as well. (I'm guessing the solution will have something to do with SUMPRODUCT?)
View 5 Replies
View Related
Jun 1, 2007
In the attached workbook, sorting the dates in column M results in absolutely nothing happening. The dates are formatted as dates (dd/mm/yyyy). The dates in column M are arrived at by adding a number of days (formatted as Number) to another date, the value of which was determined by an array formula. When I retype the actual date into another column and sort that, I get it sorted. Why does the other sort not work? BTW - I actually need to sort column M with column N.
View 2 Replies
View Related
Dec 6, 2013
I have a file from a database that includes birthdays and anniversaries. These are mainly in US format as far as I can tell, but I am trying to get these all in UK date format but when I try and change any of the formatting half of them don't seem to change.
I have attached the file : Dates.xls‎
View 2 Replies
View Related
Jul 26, 2012
I have a column for suppliers names and a total column The suppliers names differ. How can I create a separate column to show the total of spent for each supplier. I'm not up on excel code
View 4 Replies
View Related
Dec 18, 2012
How to "hard code" a cell's fill color? I want to assign a cell's fill color to yellow. While setting the fill color is easily done, my problem is that as soon as I paste data into that cell, it looses its fill color. Is there a way to tell the cell to never change the fill color?
View 1 Replies
View Related
Oct 2, 2007
Cell A1 is HEAT_CODE
Cell A2 is W5H
In cell
=DMIN(Database,"Cost",A1:A2) return the proper value
I need something like the following so I can use fill down.
=DMIN(Database,"Cost","HEAT_CODE"=A2)
the above returns #VALUE! error.
View 6 Replies
View Related
Oct 17, 2007
i have a column that is seperated by hard returns how can i seperate them into new columns.
can i find replace a hard return and then text to columns.
View 9 Replies
View Related
Apr 30, 2013
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012Â 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
View 3 Replies
View Related
Aug 23, 2009
I have a two workbooks, one which is a daily schedule and the other is a yearly summary. The worksheet in the yearly summary uses a date in the first column and the daily sheets each have a date on them. I've isolated my data in the daily schedule and I've retrieved the date in its numerical equivilant. I have data that is 6 cells wide by "x" amount deep on any given day. Essentially, I schedule different things to be made and each row has that designation, process, dimensions, quantity, etc.
I want to test the contents of the first cell in the array, copy the pertinent data, switch to the proper worksheet, and paste it into the line where it goes. I believe I need to check a few cells to narrow down exactly which group of cells on which sheet they would be copied to.
I am not trained at all as far as programming goes, but I've been practicing for a few years now. What I am just going to start doing is testing the cells in the rows in the array of data and trying to divide it out that way. This just seems like the long way around.
View 2 Replies
View Related
Feb 22, 2006
I want to be able to add a column with mixed numerals. For example: The sum
of A2:A34 with the values in the cells being 1B or 1F or so.
View 11 Replies
View Related
Oct 20, 2011
Just wanted to do some work onto the form below, move the numbers out from the mixed colum and put in a new colum, then delete all the rows if empty in "style":
The original form is like below;
StyleGarmentFabric1001
BS01Fugitive Backpack600D polyester1002BS05Cool Shuttle600D polyester1003BS07Cool Runner Backpack600D polyester1014BS13Xtreme Sports Bag600D polyester2018BS21Stealth Backpack600D polyester3008BS28Gear Sports Bag600D polyester3102BS40Recon Sports Bag600D polyester
What i need is like below:
Code
Style
Garment
Fabric
1001BS01Fugitive Backpack600D polyester1002BS05Cool Shuttle600D polyester1003BS07Cool Runner Backpack600D polyester1014BS13Xtreme Sports Bag600D polyester2018BS21Stealth Backpack600D polyester3008BS28Gear Sports Bag600D polyester3102BS40
Recon Sports Bag600D polyester... ...... ...
step by step:
stpe 1: move numbers out to a new "Code" colum;
step 2: delete the row if empty in "Style"
View 4 Replies
View Related
Jul 26, 2009
I have two macros one of which imports the data and the other processes the data. These macros were created at different times and need to be joined into a single macro that will combine their operations – with slight modification to the importing macro. Let me describe what each of the macros does:
PROCESS macro:
Cycles through the CONTROL CELL on the “1” tab using the VALUES TO TEST. For each value to test it copies the values form the output tabs and pastes them into the A+B tab. Then it removes the duplicate rows there.
Option Explicit
'======================================
Sub PROCESS() 'This subroutine is called when clicking on the first button
Dim i As Long, j As Long, k As Long 'Those are the variables that will browse through the whole sheets
Dim l As Integer 'this will be used to browse through the values to test
Dim TheSearch(1 To 2) As Object 'This is an array of objects that will be used for the search
Dim TheRange As Range 'This will be used to flag the range of duplicates in the output
'Searching for the Text string 'VALUES To TEST' in the sheet "1" in order to know where the array of values to be tested start
Set TheSearch(1) = Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count + 10, ActiveSheet.UsedRange.Columns.Count)).Find(What:="VALUES TO TEST", LookIn:=xlValues, Lookat:=xlWhole)
View 9 Replies
View Related
Sep 8, 2006
write code to post Excel data directly to Peachtree using VBA rather than the export/ import process?
View 5 Replies
View Related
Aug 12, 2007
I am attempting to sort a column with mixed letters and numbers. However, I have been totally unable to get them to sort the way I want them.
What I have is:
K600
K2
K2A
K2B
K159
K159A
K159B
K1
K1A
K1B
K428
K8
K8A
K8B
etc, etc. The prefix letter is only a K - no other letters. However, the suffix letters can be anything from A to E (at the present time)
I need to sort them numberically from K1 in descending order ie:
K1
K1A
K1B
K2
K3
K4
K4A
K4B
etc etc etc
View 9 Replies
View Related
Sep 13, 2013
I extracted data from PDF into a spreadsheet, and now I need to put all the data - according to name, email, address etc. into separate columns, so it is usable.
All categories of data are in 1 column at the moment - mixed up.
How can I sort this into columns according to category?
View 7 Replies
View Related
Oct 22, 2009
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")
View 9 Replies
View Related
Jul 5, 2013
I'm trying to put cemetery records in a simple, sortable table, names and dates. The problem is that for some entries I have a full date of birth, death, or burial (e.g. 5/12/1892) and for others only a year (e.g. 1892). Is it possible to sort these? All I can get is the just years in order then the full dates at the end. My data look sort of like this:
Smith John 1892 1/1/1940 2/2/1892
Johnson Sarah 3/12/1900 1880 3/10/1900
One thing I don't know is what number format to use (text, long date, short date....) Sometimes the date I type changes after I type it (like 1892 became March 6, 1905) and sometimes it doesn't even though the cell is the same format.
View 3 Replies
View Related
Nov 11, 2013
I have a colmun of dates of birth, how do I sort them so that they are in order of the day and not by the year?
View 14 Replies
View Related
Jun 12, 2014
In my pivot table I have "Due Date" with is a column label.
I grouped the dates into weeks by using the "days" grouping and using 7 days.
I created a slicer to allow the user to select with week they want, but the slicer is sorted as though it's a text field and not a date field.
Example:
6/1/2015 - 6/7/2015
6/13/2016 - 6/19/2016
6/15/2015 - 6/21/2015
6/16/2014 - 6/22/2014
I want the slicer to sort by actual date.
View 1 Replies
View Related
Jun 7, 2006
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
View 7 Replies
View Related
Jul 10, 2014
I'm trying to add a formula to a sheet comparing multiple suppliers rates for different items, in a table similar to this:
Item Supplier 1 Supplier 2 Supplier 3
Socks $3 $4 $5
Shoes $10 $16 $12
Pants $17 $11 $12
Shirts $79 $25 $20
I've added the usual max, min, average formulas to it, however what I want to do is create a column which will tell me which supplier has the lowest rate, therefore for it would show as a column showing Supplier 1 is cheapest for socks, Supplier 1 cheapest for shoes, Supplier 2 for pants and Supplier 3 for shirts.
View 3 Replies
View Related
Feb 17, 2014
So for example I Have three suppliers for a given delivery depending on weight to point "A" to "B"
From To AA pallet AA semi AA fullload BB pallet BB semi BB fullload CC pallet CC semi CC fullload
A B 1 2 3 4 3 4 2 3 4
A C 2 3 4 3 4 5 3 3 4
Given weight parameters that
min max mode
0 < 4 pallet
4 =< 6 semi
6 =< 12 fullload
Now I need a a table like this
Weight From to Mode Cheapest supplier pri
View 2 Replies
View Related
Apr 21, 2014
abcxyzzyz
1009080zyz
506070abc
Have a formula that quotes the supplier at cell D2 , is there a macro that will highlight the minimum value each line that is 80 and 50 to be highlighted
Yes I know =min(a2:d2) will do the work
View 2 Replies
View Related
Apr 17, 2013
I have a sheet (Data) with thousands of records (transactions) of a number of different suppliers, first I have to select all the records of a specific supplier, here I'd like to use an inputbox, and copy them to another sheet.
View 9 Replies
View Related
Jun 25, 2012
What I am trying to do is sum values for each day of the month up to a designated date.
Example:
A1=any date of the month
A2-A31 = 6/1/2012-6/30/2012
B2-B31 = values that correspond to each date
how can i sum the values in column B from the beginning of the month to A1?
View 3 Replies
View Related
Jan 31, 2007
I need to convert a column of data which will have repeated values into a column that turns that data into data with all unique values. Below is an example.
Column1Column2
AppleApple_1
OrangeOrange_1
PearPear_1
AppleApple_2
PearPear_2
PearPear_3
AppleApple_3
OrangeOrange_2
OrangeOrange_3
I had a solution until I wanted to turn this range into a list. The solution was.
=If(ISBLANK(A2),"",A2&"_"& COUNTIF($A$2:A2,A2))
When this data is in the list the <A2> in the range stops incrimenting from row to row and simply turns into the last row of the list. I have an example spread sheet upon request.
View 9 Replies
View Related
