**0**

votes

**3**answers

42 views

### In Excel PivotTables, how do I show items that occur in one category but not another?

Simplified version of my data:
item category number
apples fruit 7
orange fruit 2
tomato fruit 3
tomato vege 9
zuchini vege 2
tomato vege 17
...

**0**

votes

**0**answers

32 views

### Microsoft Excel 2007 - Auto Update in 1st Sheet based on What updated in 2nd sheet (2nd sheet Update will be in Fixed Column but Random Row)

Actually I'm facing a problem now.
I am using Excel 2007.
And, I have two different sheet of excel file with me.
My 1st sheet is my report.
My 2nd sheet is my customer name.
I hope that when ever ...

**0**

votes

**1**answer

27 views

### How can I find all cells in Excel with a particular value and get the sum of all those cells' dependents?

I am trying to find the total number of screws, nuts, washers, etc for a very large assembly. For each part in the assembly, I have the number of each type of hardware. I would like to find the total ...

**0**

votes

**1**answer

22 views

### Count Unique Cells Matching Results in Set

I've got a single column of coded data in Excel like this:
a
b
ab
bc
ad
b
ac
I know I can find the instances of all cells containing "a" with =COUNTIF(A1:A7,"*a*"). That would give me 4. I can also ...

**0**

votes

**3**answers

40 views

### Excel filter duplicate values in column A and filter out rows which have column C blank

Total n00b here.
How to filter duplicate values in column A and filter out rows which have column C blank.
I have 2200 rows in Col A (the first 1400 rows also have data in Col B & C, the next ...

**-1**

votes

**1**answer

17 views

### Find matching data in two different spreadsheets

I am a realtor with two spreadsheets. One is a list of approx 250 names and addresses of people for whom I have phone numbers but no info about home equity. I have another spreadsheet of about 1400 ...

**1**

vote

**2**answers

17 views

### How do I use a range with MS Excel COUNTIF

I have 2 columns of numbers - Columns A and B. I'd like to get a total of the number of rows in which A > B. The COUNTIF function seems right but I can't figure out how to use a range in the 2nd ...

**1**

vote

**2**answers

31 views

### Calculate the APR for a loan with an irregular payment schedule in MS Excel [on hold]

I'd like to setup an Excel spreadsheet to calculate the APR for a loan with an irregular payment schedule. For example a 12 month loan with 11 payments of $100 and a final payment of $500. I know ...

**0**

votes

**1**answer

23 views

### How to shade the area under a straight line graph in Excel

How do I shade the area under a straight line graph in Excel, along the x-axis? The graph data points are shown below.
0-16.67
170-16.67
170-6.712
187.5-6.712
652-25.352
670-33.408
910-33.408
910-0
...

**2**

votes

**4**answers

61 views

### In Excel, how to count enormous number of timestamps, and arrange counts in 5 minute periods sorted by day of week?

I've got just over 300,000 rows in a spreadsheet, and each of these rows contain a timestamp (HH:MM:SS AM) and a date (DD/MM/YYYY).
I'd like to get counts for these timestamps and group them in 5 ...

**1**

vote

**1**answer

21 views

### VLOOKUP not interpolating all cells?

My VLOOKUP formula is not interpolating all cells
AD AE AF
11-6-2014 15:20 90,4 #N/A
11-6-2014 15:21 89,1 #N/A
11-6-2014 15:22 90,4 Good
11-6-2014 15:23 89,1 Good
AF column is my ...

**0**

votes

**1**answer

17 views

### Excel 2010- long number corruption

I have a set of data, received in .csv format, in which I have a set of long UPRN numbers. I am involved in data matching processes related to new IER voting regulations.
When I open the .csv file the ...

**-2**

votes

**0**answers

11 views

### Creating line chart on the basis of full years time sheet [closed]

I am trying to make a chart with full year time sheets of all employees but unable to do it.I have time sheet of each employee including details such as name,date,clock in, clock out
Now i need to all ...

**0**

votes

**2**answers

23 views

### Error when value not present

I am using the code below several times over in one sub for different values not just "CPNEC". It works fine where there is a range with that value on the spreadsheet but falls over it the value does ...

**0**

votes

**2**answers

39 views

### I have a user that is unable to open xlsx file

I have a user that is unable to open xlsx file. Our environment is Office 2007, Win 7. Everyone else can access the file except for her. And it doesn't matter what PC she tries to access the file from ...

**-1**

votes

**2**answers

47 views

### how to create excel charts having strings in both x and y axis

I have a student data with Student Name and their grade (A/B/C/D/E.). Now I want to have a graph for this data.
Kindly help me.

**1**

vote

**0**answers

46 views

### vlookup in a cell that contains mid formula

I am trying to create a vlookup in a cell referencing a specific cell so that I can pull the data from my lookup table. Well this cell that I am referencing happens to have a mid formula in it, and ...

**1**

vote

**1**answer

15 views

### How to remove one column of a calculated item in Pivot?

I have two columns. One with data from this year and the other with data from previous year. I now have added a calculated item in Pivot to display the % delta of these two years. Now when doing this ...

**0**

votes

**1**answer

23 views

### In Excel I need to find data on sheet3 from data on sheet1

I have an Excel workbook with several sheets. The column 'SheetA'!W:W lists text that can be found within a column header on row 'Sheet3'!1:1. For example, SheetA'!W42 contains "B16". Cell ...

**0**

votes

**1**answer

30 views

### Add a row in excel based on criteria

I have a very large excel spreadsheet 2000+ rows deep.
I am organising it by adding a blank row each time the first word in Column C changes; I have been at it for over half an hour now and this got ...

**-1**

votes

**0**answers

18 views

### comparing two excel documents to match row color of first document

say i have two excell documents, .... one was sorted manually the day before, and then color coated in certian colors based on certian criteria, and the next day a different report comes out that ...

**0**

votes

**2**answers

45 views

### Explanation of the dollar sign

When referring to a cell, what is the difference between these?
A1
A$1
$A1
$A$1
For my situation, I need a formula that says "when A equals 8" but more importantly the difference between each of ...

**0**

votes

**0**answers

23 views

### Formula to match date in one column with multiple dates in other column and Increase it to next date if match is found

I need to compare individual dates in row with holidays dates in other column. If date matches with holiday, then increase the date to next date. If there are two consecutive holidays it should ...

**0**

votes

**3**answers

44 views

### MS excel - assigning “categories” based on keywords

I have en excel file with expenses(the amount of money spent is in one column), and in next column I have short description that is mostly made of multiple words. I want to "simplify" the description ...

**3**

votes

**3**answers

66 views

### How to split one row in Excel into multiple rows for database management

I have a set of data that is currently 39,000 rows and 27 columns. The first column is the ID number. The subsequent columns correspond to elections, with the date of the election in row 1 (cells ...

**0**

votes

**1**answer

33 views

### How do I use Excel to calculate turnaround time on a document using a 9-hour day scale?

I need some math help. Using an Excel log book with time stamps, I've calculated the average time it takes for us to process a document, but it's on a 24-hour scale. So if someone works on a document ...

**0**

votes

**0**answers

18 views

### How to Group and Layout Pivot Table when Values is in the Row Labels Field?

I'm attempting to present large amounts of data in a report using a Pivot Table to allow for dynamic filtering/categorization. The data in the table is of the form:
Year Month Category Region ...

**0**

votes

**2**answers

30 views

### How to shift unique values down a column to the same value in another column?

My friend has two columns where one has unique values and another column has those same values but also duplicated like shown below
Column 1 Column 2
ABC123 ABC123
CDE456 ABC123
EFG789 ...

**0**

votes

**0**answers

15 views

### Excel 2013 Chart - Multiple Series on Pivot Chart

I have an Excel 2013 file that pulls data from a SharePoint list. The goal is to create a dashboard that can be refreshed easily and without formatting or updating formulas of any type.
The ...

**0**

votes

**2**answers

39 views

### Excel bar graph, easy idea, hard implementation apparently

I want to make a graph like this, but I can't seem to make it correctly:
Data: 4, 2, 3, 4, 1, 3, 3, 2, 3, 4, 2, 3, 2, 1
Three elements: Y-axis potential values (1-4 or worst, below average, above ...

**0**

votes

**0**answers

19 views

### Excel 2D-lookup on a PivotTable with changing coloumns and rows

I want to do a Vlookup on a 2D pivot table but neither the coloumns and rows of the pivot are fixed and are subject to change due to ever changing data source. How can I perform a accurate vlookup on ...

**-1**

votes

**0**answers

20 views

### Split apart and remove data

I receive orders for telephone service for between 15 and 30 stores in a single email in regular text format. The stores are separated in the email by one blank line. Each store begins with ...

**0**

votes

**0**answers

9 views

### Method to export material 'group' data from Quickbooks Manufacturing and Wholesale

Quickbooks M&W stores bill-of-material data in 'groups' and I would like to export that information hierarchically using Excel. Does a report exist in QBM&W to perform this action?

**2**

votes

**0**answers

37 views

### Is there a way to automatically display the file path on every print whether its Word, Excel, PDF

I am working with one of our help desk tickets and I personally don't think it's possible unless you go through some crazy scripting... The help desk ticket client was asking if is there a way to ...

**1**

vote

**2**answers

47 views

### Excel: how to permanently apply conditional formatting to the whole spreadsheet?

I have the following annoying problem with Excel 2010. I use Conditional formatting to apply bold formatting to each line of the spreadsheet that meets a condition expressed by a formula. In the ...

**0**

votes

**0**answers

15 views

### VBA Compare columns on two diff worksheets, find in 2nd and group on 2nd worksheet

Excel 2010
Hi. I have a 6,000 line spreadsheet and need to group the lines, based on identified roll up points in another spreadsheet (150 roll up points). I want to be able to see the summary ...

**0**

votes

**1**answer

16 views

### Last occurence in a “right vlookup”

I don't know if the question must be put here or on SO. Sorry if i make a mistake.
I have to search the first and last occurrence of a certain value within a column(array).
I have managed to make use ...

**0**

votes

**1**answer

30 views

### Excel chart axis with personal label instead of number

I'm a simpleton with excel but writing a research dissertation. Much charting involved...
My y axis has values 0-6 but each number represents a patient response (for example never, very rarely, ...

**1**

vote

**2**answers

25 views

### String Manupulation in VBA

I have single column that needs to be split to multiple, like Text-to-columns in excel. However there is a small challenge. Conventional delimiter will not work. Consider the string below
Original: ...

**0**

votes

**0**answers

9 views

### Why is that excel refueses to update a refrence after it has been assigned a long time ago?

For example I assigned and placed some values and formulas in a spreadsheet, but after a while, not more than three to four weeks if I now place another figure in between these values and the answer, ...

**2**

votes

**1**answer

27 views

### In Excel, can you create a cell that updates the number of another cell without having to hold the inputted value?

Since I can't create a form using VB or ACCESS here at work, I'm trying to figure out if I can use a cell as a makeshift form. I want to label one cell "Enter New Order Quantity." Next to it(next ...

**1**

vote

**2**answers

16 views

### Rolling up the status of an excel column to a single cell

I am an intermediate excel user at best. I've looked everywhere back can't seem to be able to find this specific formula how-to.
What I need to do is in a status worksheet I have a series of tasks ...

**0**

votes

**1**answer

37 views

### Rolling Averages sheets in excel

I'm collating a document for a Daily Production report at work, in which every day, a new tab is added and the information on part production from each department for the previous day is recorded. The ...

**-1**

votes

**2**answers

55 views

### Excel how to divide a time period into equal time periods

I would like to find a way of dividing a period of time into 4 hourly time zones. I.e. if a piece of equipment is unavailable from 0900hrs to 1700 hrs that is 8 hours but I need to show this in 4 ...

**1**

vote

**1**answer

29 views

### Conditional formatting excel conundrum

I have a list of values in excel.
I would like to highlight a cell if its more than 1.5 higher or lower than the value before it or after it.
Is this possible?
Thanks guys!!

**-1**

votes

**2**answers

35 views

### Is there a formula to change the value of a call, depending on selection from drop down list?

I am trying to create a prices spredsheet.
I need one column where you can choose a product from a drop down list
The next column you enter the amount of units required
The next column (the tricky ...

**0**

votes

**3**answers

28 views

### How can I modify a lot of values easily in Excel?

I have a big file of numerical data with 600 columns and 4000 rows. I would like to be able to change the data of about 3/4 of the columns by multiplying them with 1.02. For example, let's say I have ...

**0**

votes

**1**answer

34 views

### How to make a named cell read-only

I would like to make a cell "Cell Name" read-only.
I found this code here but don't know how to use the String value instead of the range:
Private Sub Worksheet_SelectionChange(ByVal Target As ...

**0**

votes

**1**answer

27 views

### excel spreadsheet average with prioritization

I need help how to write excel formula (or any excel solution)
to sum with priorities.
I have transaction data like this:
Date Unit Price Buy/Sell
7/11 3 10 Buy
7/12 2 9 ...

**0**

votes

**2**answers

25 views

### Using Index Match Conditionally to Indicate Whether

I feel pretty comfortable with INDEX MATCH, but I came across a problem I'd like to use it on where I'm not quite sure it works. I have two sheets: the first smaller sheet has a small subset of ...