Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [microsoft-excel-2010]

The 2010 version of Microsoft's spreadsheet application.

Filter by
Sorted by
Tagged with
1
vote
1answer
22 views

Calculate Gregorian Julian Date from Regular Date in Excel

I am trying to get a Julian Date from a regular date (mm/dd/yyyy) in Excel. There seems to be two kinds of Julian date calculation. One is described Here.. The other is somehow related to Gregorian ...
1
vote
1answer
22 views

Chrw() Anomally: Invisible Values

I found an anomaly that I don't understand. I generated a list of special characters (into a column of cells) using the Chrw() function and the list was created just fine. However some of the cells ...
0
votes
0answers
59 views

How can I fix my Profit Margin formula? [UPDATED]

I have created a model whereby I've got the following: Cost (column P) Dealer price (Column S) Current List price (Column T) I've then done some market research and have adjusted List pricing (RRP) ...
1
vote
1answer
117 views

How to reduce number of formulas and columns?

My goal is to adjust my pricing to match that of the competition, be it by reducing or increasing my current pricing. My model is a bit complicated and messy because their pricing includes tax, while ...
0
votes
1answer
27 views

Count of number of times a max of the dates in two columns is bigger than the date in the third column

I have three columns in a report: a closure date (Col A), an implementation date (Col B) and a target date (Col C). I need to count the number of times an entry is closed or implemented before the ...
0
votes
2answers
88 views

MS Excel - How to drop the character between the concatenated cells

I need to concatenated cells and put a comma between them. But some cells in the concatenated columns are empty and the result shows the commas between them. How to drop the commas if a cell is empty? ...
2
votes
3answers
109 views

Check if cell text has a specific format

I am looking for a way to determine if text entered into an Excel cell is following this format: "0000/00/00" (0s can be any one digit number {0,1,2,3,4,5,6,7,8,9}). Is there a way to check if the ...
0
votes
1answer
53 views

Match the entire row from one table with another and then return a value from table 1 in Excel

If I have two tables, Table 1 has the following data *** CTRL Number DATE MEMO DOLLAR 1 4/4/2014 "xyz" 505050 2 4/5/2014 "fadf" 99999 3 ...
0
votes
1answer
51 views

Excel parent child hierarchy

I have some data and I need to create a hierarchy of data. prdCode is the child and the ID; prdParent shows the parent of the data; prdLevel show what level the data must be at. 0 is the top. I ...
0
votes
0answers
15 views

I need to assign a number to a letter

I would like to allocate a number to a letter in excel. For instance, if I type a, it must equate to 33, b must be 44, c must be 51, and so on. How do I do this?
1
vote
1answer
101 views

EXCEL - How to use VLOOKUP to get the entire table contents?

I have an Excel sheet with columns: Screenshot Data Table 1 Table 2 METADATA Date ...
1
vote
1answer
12 views

Replacing Date Format in multiple Columns

I am trying to edit a CSV file in Notepad++. I have about 500 lines of dates in a format that I need to change to a different format, so basically, in the CSV file in Notepad, the dates are shown as ...
1
vote
0answers
13 views

Secondary horizontal axis: Wavelength (nm) and Energy(eV) on the same plot in Excel?

Is it possible to create in Excel a dual-horizontal axis plot, showing on the top horizontal axis the Energy and on the bottom the Wavelength (both on x-axis)? The energy is related to wavelength ...
2
votes
0answers
9 views

Excel Power Query

Want to create 3 queries that have a column filter in them. Rather than set the filter value by using a hard value I would like the filter value to be decided by a cell value in the workbook. So ...
1
vote
1answer
33 views

Excel 2010 autofill option by entering last four digits of 10 digit number how to

I'm having list of 1000 numbers (10 digits) in each rows, need to get(retrieve) that particular number from that data by entering last four digits alone.. Just I have to retrieve the 10 digits by ...
0
votes
0answers
10 views

VBA - Extracting data from dictionary [migrated]

Stuck on this for 8 hours! Please help.... My database has 100 columns, with column A being a unique list of employee ID and column K being each employee's pay status (exempt, hourly, non-exempt, etc....
0
votes
1answer
30 views

Convert a column of 1s into a timetable

I am looking into the possibility of converting a column of 1s with headers into a timetable. I have attached a snippet of what the problem will look like. I have a row of 1s showing where a potential ...
0
votes
1answer
20 views

Excel return columns with row containing certain values

Nice easy problem i'm sure. I want to be able to return a column of values from a list of columns where a row value is greater than 0. from the picture below you can see that column GX on row 5 is ...
1
vote
0answers
18 views

How to search for and return all records between two dates?

I'm not sure if this is a lookup formula I need. Basically, I have a huge spreadsheet with the travel details of lots and lots of groups. Each row is a different group. For reference, Column K shows ...
1
vote
1answer
50 views

Finding the Closest/Nearest value in relation to X and Y Coordinates Excel

I have been struggling with an issue for days now. I have an excel sheet with a lot of data about Airports in Europe. For simplicity sake, i will narrow it down to few columns which are all in the ...
3
votes
1answer
36 views

Force Excel to re-parse a whole column of dates

I have a series of dates in a column, currently recognized as text. Thus sorting doesn't work correctly. When highlighting the column and changing the format to Date, Excel still recognizes it as ...
0
votes
1answer
41 views

How can i simplify my code using LOOPS in VBA

I need to copy contents from a column in one worksheet and paste it into another (at the same time making a total of 7 copies of each of the variable). There are multiple columns and each column has ...
0
votes
0answers
22 views

Lookup in Tree style table in excel using index match

Company | Sector | Date ABC XYZ Q1 2019 Q2 2019 DEF KBP Q1 2018 TGH LHK Q3 2020 Imagine "Company" is Cell A1 and "Q2 2019" is Cell C3. I would like ...
0
votes
1answer
33 views

How to group duplicate value from pivot table in Excel

I have an excel pivot table but it has records over 1036 records. I tried to use the method like other tutorials such as distinct count but it does not seem to work. How would I be able to group those ...
0
votes
1answer
33 views

How to make calculations with specific lines?

I have basic to medium knowledge in Excel, and I am having trouble with a somewhat complicated problem for me. I have a table where I need to relate three columns: a "Type" column, and another two ...
1
vote
2answers
24 views

Excel 2016 - How to bring up a conditional list

I'm trying to create a dependent list in Excel 2016, but don't really know where to start. I tried following a couple of guides to create a dependent list, but it wasn't doing what I wanted it to do. ...
0
votes
0answers
23 views

Categorizing Spend in Excel based on Trends in a Number of Cells

I'm looking to create a formula, if possible, to identify trends and as a result return a specific category and sub-category. I'm not sure if this is possible so any help would be greatly appreciated....
1
vote
1answer
107 views

Devising Excel Spreadsheet Formulas

I'm using Excel for the accounting purposes of a group venture, to keep track of our expenses, costs, payments, and balances. Details of each expense are stored in columns A:J Total costs and ...
2
votes
0answers
28 views

Searching for possible solutions/workarounds to creating a Master Excel workbook

I'm trying to find solutions for creating a master template in Excel. We have almost 100 Excel workbooks where data in them varies but the formatting and cell and row layout including header names ...
0
votes
1answer
24 views

Create matrix from data list in MS Excel

I have a list in MS Excel that looks like as follows: 735 1081 1377 2899 1318 723 1105 826 1126 792 761 1674 Now I want to make a matrix as follows: First 6 values for the first row from 2nd value ...
0
votes
0answers
36 views

Brining back value that matches a number

I am trying to bring back a specific value from my worksheets Worksheet 1: Worksheet with columns Survey_ID and Credit Card Worksheet 2: Worksheet with Columns PII_ID, Survey_ID, Name, Credit Card ...
0
votes
2answers
25 views

Formula Assistance to subtract a certain number based on the code IF?

I need to subtract a certain amount based on which code is in column H. If 97530 then minus 96.40 If 92610 then If 92507 and 92526 then I know how to create my calculation formula but I can’t for ...
0
votes
0answers
23 views

How can I build a dataset of add values in past?

I would like to add value to the cell and keep a record of which values I have added in the past. With the help if VBA. In short, the value I add to the cell, How can I build a dataset of add values ...
2
votes
1answer
63 views

Is it possible to use an Excel function to evaluate criteria using strings similar to how COUNTIFS() and SUMIFS() does?

My issue is that I have a criteria written as a string in a cell, for example ">= 50" is in Cell A1. I am able to use this with SUMIFS and COUNTIFS easily since these functions take the boolean ...
0
votes
1answer
19 views

How do I remove duplicates while adding values at the same time in Excel or Google sheets?

I'm very new with spreadsheets and I'm dealing with a very large data set and I need to filter the generated duplicates. I got an A column which lists products and a B column which lists the amount of ...
0
votes
1answer
15 views

ISBLANK for checking multiple cells for any data and then using that data in my cell

I have the following scenario: I have drop down menus on another tab for people to select from several types of screw. There are 6 different cells that could contain some data. It should never be the ...
0
votes
2answers
48 views

Excel - If any of 6 cells are > 0 divide by that number of cells

What I'm trying to do is the following: Once 'Cell B19:B20' and 'Row 21' have a value, 'Row 22' divides 'Cell B19:B20' between the amount of columns that have a cost value ('Row 21'). For example: ...
1
vote
0answers
22 views

Excel uses custom date format in new workbooks for newly entered dates

I am using Excel 2010 on a US windows 7 machine. I made a new default macro enabled template for Excel to use for all new workbooks. When I make a new blank workbook and enter a partial date like 8/13 ...
0
votes
0answers
25 views

(Excel) How to use tags separated by commas and filter by tags on another sheet

I'm building my own database for a few products I use, and for one category (uses) there are several possible uses for any given item, so I want to separate them with commas. On another sheet, I have ...
0
votes
0answers
31 views

How do I paste time into a time data validation cell without error?

For most data validation cells with drop down lists, I find that if I paste one of the values in the list exactly, there's no error. However, with my drop down list of time values (Ex. "5:00 PM", "6:...
-1
votes
1answer
66 views

VBA to copy entire cell values to clipboard [closed]

Sub Test() Dim copycell As DataObject Dim ws As Workbook Dim strText As String Set ws = Workbooks.Open("C:\Users\Desktop\New Microsoft Excel Worksheet.xlsx") strText = ws.Sheets("...
0
votes
0answers
14 views

Paste specific cell value repeatedly into another workbook depending in last used row

I have 2 books "Sales Invoice" this changeable as per invoice I'm doing & " Sales details" where I save all invoices' details into, like "Name, Unit, price, Qty,...." I have a code to copy invoice'...
0
votes
0answers
17 views

Excel PivotTable: Collapsing/grouping a multilevel column

Hopefully this is pretty straightforward. I have a table with Date, various expenses, and Type. Type only has two possible values: "Estimate" and "Real". In my PivotTable, it is organized as: ...
0
votes
0answers
18 views

How can I get, in R, features of Excel cells?

I know, and use, several R libraries to read Excel sheets. But all of them seems to get only the content of the cells and I wanted to get also some feature informations (like text color, background ...
0
votes
0answers
17 views

How to sum only values in one column that have a unique value in another column? Formula not working

I found the post from 2017 and have attempted the formula. Unfortunately, I am receiving a generic error. Nothing appears wrong to me. Can you help? =SUMPRODUCT(N(Details!G3:G446<>Details!...
0
votes
1answer
58 views

Transfer data from one file to another in Excel automatically

I have two Excel files, the first file contains about 5500 cells, the second file is empty and I want to transfer the contents of each sheet in the first file to the cells of the second file. Clearly,...
0
votes
1answer
194 views

Combining a clustered column chart with multiple line markers plots in Excel 2010

Is it possible to combine a clustered column chart with multiple (line) markers plots, so that each markers plot is 'aligned' with one series from the clustered column chart (in Excel 2010)? Just to ...
1
vote
2answers
65 views

Calculate the number of pay periods in a given span

I am trying to determine potential liability in an employment case. For each pay period in which work was performed, each employee is due a penalty amount. Even if they only worked a few days, they ...
0
votes
0answers
89 views

Excel VBA - loop through combinations and formula and display values that meet criteria

I'm new to excel VBA, and this is beyond my scope. I'm hoping someone can assist. I have 3 input (user input) variables to start with (this number needs to goto Nth number). I have a target number ...
0
votes
2answers
114 views

Excel: lookup the last non-zero, non-empty cell in filtered range

The following formula gives the last visible data in a filtered range: =LOOKUP(2,1/SUBTOTAL(2,OFFSET(R5,ROW(R5:R200)-ROW(R5),0)),R5:R200) However, it'd return zero if the last visible cell contains ...