Ok just started using Excel 2013.
Trying to sort 3 columns:
Column 1:
A1/B1
A2/B2
A3/B3
Column 2:
Team 1
Team 2
Team 3
Column 3:
1
2
3
Column 3 is just a ranking from 1-30. So I select all the data in columns 1 and 2 and hit sort from largest to smallest and it looks like excel computes, but nothing changes and it doesn't sort from largest to smallest? A
I am using Excel 2013/365 on a Windows 7 OS. Recently (today) I added a command button to a worksheet, opened the properties window and tried to change the Backcolor property. When I clicked on the dropdown arrow no list appeared, it highlighted the default in the designated area. I then tried the Forecolor property - same response. I tried all the other dropdown arrows and they worked perfectly. To me I believe I may have inadvertently hit a key to disable these two color property dropdown arrows
View 1 Replies View RelatedI created an add-in with custom VBA and forms, which also stores user preferences in worksheets in the addin. When the add-in loads, it creates a custom menu on the Add-Ins ribbon that allows the user to run the main macro, or to assign a shortcut key of their choosing. It also allows them to enter registration information.
In Excel 2013, the code is all accessible since the add-in still loads, but it doesn't add the custom menu allowing users to interact with the macros. I'm told this is due to the switch to SDI.
It seems that adding Workbook_Open code to a normal file allows the custom menu to be created, but adding the same code to an add-in file does not work.
I am working on a commission report for our veterinarians. I have a tab/sheet for each year, with a column for each month, a row for each of the veterinarian. Each vet is on the same row, year to year. I need to be able to calculate the %, year to date, for each vet, across the past 2 years. I can do the auto sum, but do not know how to do percentage. I have row 3, column b3 thru m3. I want to do 2012 and 2013. Each sheet is name the year.
View 1 Replies View RelatedExcel 2013
Our company has a shipping report spreadsheet that has the job number in column A and the ship date in column F. I want to track other things on a separate spreadsheet. So far, we are only down to row 1440 so in order to account for future jobs, my code on the other spreadsheet is:
=VLOOKUP(A2, '[Shipping Report.xlsx]Monthly shipping schedule'!$A$1:$F$5000, 6, FALSE)
Also, the jobs are arranged by ship date, not by number order.
This has worked perfectly for months but for some reason, it will no longer recognize job numbers greater than 1331. The format is the same between the working and non-working cell. Is there a max limit for vlookups that I am not aware of? If this was a sudden failure, I could nail it down but there has to be an underlying reason I'm not seeing.
Here is a sample of my spreadsheet:
Ship Date
1325 12/27/13
1326 3/10/14
1327 1/4/14
1328 1/31/14
1329 3/3/14
1330 1/22/14
1331 1/15/14
1332 #N/A
1333 #N/A
1334 #N/A
1335 #N/A
1336 #N/A
1337 #N/A
1338 #N/A
1339 #N/A
1340 #N/A
I am currently using Excel 2013 inside of Windows 7.
I have 3 different data sets and in each data set the only unique identifier is the card number. I currently have to manually sort each of these fields because of the different variables in each data set. My overall goal is to align every user across each row so I can verify whatever status I have for the Card # in the first data set is the same across the other data sets. Is it possible this process can be automated by using a VBA macro that sorts, aligns and leaves empty spots when needed. Once again the only unique identifier that matches across all data sets is the Card #.
User Name
Card #
Status
User Name
Card #
Status
User Names
Card #
Status
John Smith
12345
Active
John Smith
12345
Valid
John J. Smith
12345
In Use
[Code] ..........
I have a filter on the Qty column but when I sort Z -A or A - Z it does not work. I have tried formatting as number but no success, I have never seen this before, why the sort wont work? I am using Excel 2010 in Win 7
View 5 Replies View RelatedI have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
Does excel 2013 have a fence way to sort a list of alpha numeric numbers and alphabetize in this order A - Z and then 0 - 9.
Ex) Apple, Greg, Rob, Sand, 123, 126, 1000, 2001
vba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.
So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.
My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j.
I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.
Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below
[Code] .....
IIn my example I have result data from the 2013 USPGA Tour, ordered by player (column A) with subsequent columns detailing their finishing position in each event.
I simply want to condense each player's performance data into one row - as doing it by hand after each event is very time-consuming.
Is there a way of using a simple formula, macro or pivot table (or whatever) to merge each player into one row, but keep each column in the same position (ie to correspond to each weekly event).
I am using Excel 2013, and I am following the example here: VBScript Scripting Techniques: Read Excel files without using Excel that reads in Excel data as an ADO record set to a classic ASP file using VBScript. I am not able to import all the Excel data successfully, and I need to know what I'm doing wrong. Note that in all these samples cell A1 is the heading text "Column1" and the main data starts on cell A2 (consistent with the example code).
When my source Excel data looks like the following:
Code:
Column1
1
2
3
4
5
6
7
X
9
10
It imports everything OK. However, if I move the X to the next row:
Code:
Column1
1
2
3
4
5
6
7
8
X
10
...the "X" cell gets imported as an empty string. So the imported array looks like this:
Code:
arrSheet[0][0]: Column1
arrSheet[1][0]: 1
arrSheet[2][0]: 2
arrSheet[3][0]: 3
[Code] ....
But if I add another X to an earlier row in the source worksheet, like so:
Code:
Column1
1
2
3
4
5
X
7
8
X
10
...this gets imported OK.
I'm trying to figure out how to copy raw data from one file(emailed to me) and paste it to my existing file "File b" into a table "tbl a"(to make it dynamic). From another table "tbl b" on another sheet within in "File b" I want to auto populate "tbl b" with all the records from "tbl a" but not all columns from the records. To make it more difficult, I want to edit some of the data and the headings between the two tables are not the same. Example below.
Ship-to-name
Product Name
Date Shipped
Customer Group
ABC-Atlanta
Advil (Ibuprofen) 800MG
11-15-2013
Wholesaler
[code].....
Notice column b has different heading and the data need to be modified. Column d is not needed at all.
Also this need to happen when the raw data is copied into "tbl a" and again all records need to be copied over with changes.
This also needs to be done outside of VBA if possible using just formulas and possibly filtering.
I'm using MS Excel 2013
How do I transpose a horizontal reading excel into a vertical reading excel and transpose all of the data and formulas?
View 1 Replies View RelatedExcel 2013 on a surface tablet and attempting to create a data form. I've followed the necessary steps to try and add the "Form" button to the quick access toolbar, but "Form" is simply not a listed command. Have looked in "All Commands", "Commands not listed in the ribbon" and "Data Tab" and it's nowhere to be found. Not greyed out, just not there.
Frustrating because it's so easy to create a form on past versions of excel. All the tutorials I've seen online explain how to add "Form" to the quick access toolbar
I have to import data from an external source(oracle database) to an Excel(2013) table.
Now the data in the staging table in the database keeps refreshing/changing, However in Excel i need the data to come into a new row everytime instead of refreshing the whole table and looking like the staging table in the database. So basically i need to build history in Excel.
I'm having trouble filtering a large list of 900 names to create mailing labels for anon-profit organization. For years I have been using Microsoft Works to create these labels, however, the people now doing the membership insist on using Excel. I have Office 2013 and am using Excel 2013 on a new Dell PC using Windows 7. I have partially solved the problem but have one hang up.
I got to a point where I could filter out the dates and a couple of other items, but can't seem to get the last two. I was able to get the minimum date (equal to or greater) than 2012 to filter and the (equal to)LIFE (life membership) one and the (equal or greater than) ID# of 9000to filter by changing the cell format in all of those columns of cells to "text" instead of "general", BUT I still can't get it to (be blank) for the M column which houses a "D" or "U" (indicating deceased or uninterested) and an E column which houses an "E" if the person receives the newsletter electronically rather than by mail.
My fieldset up is:
To Year is equal or greater than 2012
Or M Class is equal to LIFE
Or ID# is equal or greater than 9000
And M is blank
And E is blank
The first three work but the last two do not seem to filter properly.
I have made a Rota of sorts using Excel 2013 Desktop Edition for my charities volunteers (and stored it as a shared file via office 365 server that they can download and edit) and this rota is populated by our volunteers manually. Each day our controller needs to check the file to see who is on duty at that time.
How the Rota is populated.(the bit i managed to do myself)
The volunteer (Person A in this example) would open the excel file and go to the month they wish to choose a shift for (ref worksheet: FEB in this example). They would then pick a shift that suits them and click on the cell (ref: F32) that shows a vehicle available, then from the drop down list they select their name and then save and close the sheet.
Rather than our volunteer controller going through the sheet for the current month (ref worksheet: Sheets JAN to DEC) I would like them to use the first sheet in the workbook (ref worksheet: DC Info Page) to get an instant view of which volunteer is currently on shift.
My current problem
I don’t know how to make the excel file do the following
Search sheets JAN to DEC (ref cells: C4:I58 on each sheet) inclusive for the cell that contains today's (current actual) dateCopy the 8 (eight) cells below the cell that contains today's datePaste the copied cells in to the relative cells (ref: C8 to C15) in sheet one (ref worksheet: DC Info page)
I would also like this to be done automatically so the controller does not have to click on anything after they open the file. But if it needs a button to process the request, one could be added to the worksheet (ref: DC Info Page)
I have created an Excel spreadsheet in 2007. When a friend opens the workbook in Excel 2013, It does not let him add data to the cells. ( just one column which is the "date" column) The worksheet is not locked or protected.
When I right-click on the column and goto format cells, protection, the box is ticked, but the note says this doesn't take effect unless the sheet is protected witch it isnt.
I am using Excel 2013 and would like to input data into my spreadsheet using a barcode scanner. I've created some barcodes using an online barcode generator (Code 128-B). My barcodes are text as opposed to numeric. I initially tested it out using Access and it scanned the information just fine. However, when I try to scan in a code into Excel nothing happens, no error, no data, no nothing. Is there something I need to set in the options of Excel to recognize the barcode scanner as the input device? Or is there some other reason why the scanner might not be pulling in the data?
View 3 Replies View RelatedI've got large set of data(Column E) for dates from 2007 to 2013(Column B). Here dates are in chronological order.(Attachement) Now I want to match the data in to dates in Column H. Here dates are in random order. I used =INDEX(E3:E40582; MATCH(H3;B3:B40582; 0)). But it doesn't work.
View 5 Replies View RelatedPlease see the attached example. I'm trying to highlight cells only if 2 values in the row match 2 values in another row. If a book title/author combination matches that of another title/author combination in a different row, it would highlight. Some alternate rows will contain different titles by the same author, and some rows will have like titles by different authors...but they should not highlight.
The only situation where highlighting would occur is if the title/author pairing appears in another row.
I'm trying to decide if this would be a conditional formatting/highlighting rule, or if it would be a macro/vbs?
I'm using Excel 2013.
Pairing example.PNG
I have several competitors balance sheets (around 15), they all have the same structure, what i will like to do is summarize all this data into a pivot table to have a grand total but also be able to filter the data by single competitor.
I have tried to do multiple consolidation ranges, power pivot, pivot but i was unsuccessful, maybe i arranged the data wrong or im not using the right solution. im using excel 2013
I have 2 columns with a list of competitors (competitor 1 & competitor 2) involved in a negotiation + the price/value of the negotiation. Each line represents a negotiation with a value in numbers & the names of the 2 main competitors involved.
I have around 150 lines in the original file and would like to show in a graphic what are the competitors that we regularly find in the negotiations and what is the value of the negotiations they are involved.
The problem is that there is no main competitor so i can find the same name in any of the 2 columns and i cannot make separate graphs for each column because if i do so i duplicate the value.
Is there any way that aggregate this info into a single graphic/pivot graphic? Im using excel 2013
I am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
I have a database in Excel 2013 and now I want that when a value (a person's name) is entered in a cell. That then the database sort of filters the list for me, so it's still possible to make changes in the entries.
[URL]
Picture above to specify the search, which I would therefore like to edit
Dashboard_Action Pool Team 7.2.xlsm
I have been all morning working on a simplified version of the tutorial from YouTube: Create your own Excel Search Pt. 4. But came back later so only then that I can not change the data:?
I work for one half of a joint venture & am responsible for planning & expediting. The other half does purchasing. The bi-weekly PO download reports I receive are less than useful. I have already written the code to delete undesired sheets & add, format, and enter headers for a "Summary" sheet.
I need code to move to the next row, and run formulas to pull data from the next sheets, and repeat until there are no more sheets.
The number of sheets will vary from one download to the next, and the sheet names will vary from one download to the next.
Following are example formulas that need to be run on successive rows while pulling from successive sheets.
I am running Excel 2013 on Windows 8.1
I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.
Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.
I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.
Is there a formula/script for me to skip the correct number of rows to the next data block?
I have attached my spreadsheet to this thread. I am using Excel 2013
FeS_Kinetics.xlsx
I want to analyze a survey which I made. Below you see an example of how I structured the answers.
Now I would like to rank the answes (rows 2-6) in a list according to often each item was mentioned.
I could do transpose the data manually and delete items which were mentioned various times. However, since it was a pretty big survey, it would take to long. Is there a formula to do it?
Original:
Person A
Person B
Person C
Water
Water
Sugar
Washing
Washing
Pasta
Boxes
Milk
Water
Frozen
Vegs
Fish
Cleaning
Cheese
Water
Example of how it should look like:
Water
3 (times mentioned)
100% (because everone mentioned it)
I'm highlighting a range and then sorting column F from Largest to Smallest. The macro doesn't sort it at all. Added my code tags.
View 1 Replies View Related