# Questions tagged [microsoft-excel-2010]

The 2010 version of Microsoft's spreadsheet application.

4,415
questions

**1**

vote

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**3**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**0**answers

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**0**answers

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

**1**answer

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

**0**answers

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

**1**answer

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

**0**answers

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

**2**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**1**answer

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**0**answers

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

**2**answers

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 ...