All Questions
105
questions
4
votes
1
answer
183
views
Consolidate Data from Multiple Columns into One Column
I have a dataset that looks something like this:
Customer
Col1
Col2
Col3
Bob
111
2221
Michael
112
Anna
113
2223
Rachel
114
2224
334
Person5
115
I'd like to put all data into one column, ...
0
votes
0
answers
26
views
Importing a range of formulas from csv or txt file starts with ' in excel and sheets [duplicate]
I am generating some image URLs with a python script and converting them to a formula. I have tried saving it to both a CSV and a TXT file as others suggested, but both lead to the same problem:
After ...
0
votes
1
answer
72
views
How can I get an aggregated count of rows grouped by two values and enforcing uniqueness on a third in Excel?
I have a Google Sheets spreadsheet which is connected to an external integration that automatically populates rows with new 'events' contained within one of the sheets. I want to create a second sheet ...
- 1,708
-1
votes
1
answer
53
views
Alpha-sorting multiple rows, empty cells for missing values
Say I have multiple columns:
A | B | C…
Bill | Bill | John
Adam | Fran | Nick
| Adam2| Adam
I’d like to alpha sort the rows in each column, and also add empty values in a cell if the cell ...
- 109
0
votes
1
answer
2k
views
MAX function not working on date with format dd/mm/yyyy in Google Sheets (Excel)
I'm using Google sheets and the MAX() function works only on dates with format mm/dd/yyyy. How to get the MAX() of date with format dd/mm/yyyy?|
- 3
0
votes
1
answer
609
views
Google Sheets - Making a chart where certain series do not have data for all X-axis values
I am making a chart for research in musicology which requires me to graph the duration and tempo of notes based on the score, i.e. musical values like a quarter note.
Using a recording of a string ...
-2
votes
1
answer
761
views
Excel / Sheets: How do I filter addresses based on a list of zip codes?
I'm planning to mail some postcards to potential business contacts.
I've got a spreadsheet of 2,500+ addresses with columns including Name, Street Address, City, State, zip code, etc. that cover the ...
0
votes
1
answer
2k
views
Find next cell within column that is a number, starting from reference cell in another sheet
I think this sounds simple but I'm having a bit of trouble figuring it out.
I have 2 sheets, the Sample Sheet attached and an Input Sheet.
In the Input Sheet, I input one of the green shaded values (...
8
votes
4
answers
9k
views
Formula to see if a series of numbers is increasing in value?
I have a spread sheet with thousands of sets of numbers. That is too say, there are ten columns and each column has a number - and there are thousands of rows of these numbers.
What I want to do is ...
- 225
1
vote
2
answers
477
views
Using XLOOKUP or FILTER to aggragate data into a single cell
I have a data set that is compiled by various people filling out a form to populate a spreadsheet on Google Sheet. Here's what the data set might look like:
A data set listing out name, collected ...
- 13
-1
votes
1
answer
582
views
Excel: How to count how many times a consecutive text value appears
I'm making a work schedule and I have to pay attention to a specific rule that, no worker should be scheduled to work more than 6 days in a row.
I got this spreadsheet where I make those schedules but ...
-1
votes
3
answers
2k
views
Excel: How To Transpose Every Random amount of N Rows From One Column To Multiple Columns?
I have a set of multiple choices questions with ABCD as answers, all answers are stacked on a column, but not every question has the same amount of answers.
Instead of transposing every nth value, my ...
1
vote
1
answer
4k
views
Is there a formula for calculating the difference in minutes or hours between two times?
Is there a formula for Is there a formula for calculating the difference in minutes or hours between two times in two columns?
In my spreadsheet I have the following two columns called start time and ...
- 2,204
0
votes
1
answer
38
views
How to get Project Wise Summary numbers from one Google Sheet to another?
I am trying to create a Project accounting Google sheet.
In one sheet I have all the expenses, revenues for the projects. (see below)
and the other sheet will have list of projects with Summary of ...
- 323
0
votes
1
answer
309
views
Search across cells and return value from different cell
I need to reference a cell based on drop-down values from four different columns.
Essentially I am trying to compile a summary list of data from different tabs. Each tab is laid out the same however ...
- 3
1
vote
2
answers
28
views
Sequence Generation formula in excel for an “exploder” function
I am wondering if there is a way to create the following type of sequence in excel:
1 1
2 1
2 2
3 1
3 2
3 3
4 1
4 2
4 3
4 4
5 1
5 2
5 3
5 4
5 5
I would ...
- 13
0
votes
1
answer
35
views
Excel/Google Sheets: What is the best way to continue this formula so it will include cells yet to be filled in?
I'm manually calculating the variance and standard deviation of a set of potential investment returns, each with their own probability of occurrence.
Assuming I'd like to expand this list and not have ...
0
votes
2
answers
141
views
How to get "winner" based on row MAX
I'm trying to get the header value based on the highest value per row.
For example, given a sheet like:
A B C D
-------
1 2 3 4
3 3 1 2
I'd like to do reports on the winners of the row. For example ...
- 285
0
votes
1
answer
163
views
How do i create a Dynamic Dropdown Data Validation List based on pre selected criteria?
I seldom use Google Sheets or Excel, but now that I'm working on a card game, I have since thrusted myself into formulas and data validation.
I'm currently running into a logic issue.
I would like to ...
0
votes
1
answer
26
views
Time since previous order
I need to go through a big amount of data for the last 2 years in a company and calculated the time since previous orders for Users who have made multiple purchases. Which Excel function can be used ...
0
votes
1
answer
46
views
Return X amount(s) of cell values under under specific cell address
To start, thanks for reading, I will be leaving this picture here, which is what my current sheet looks like.
I'm trying to parse the information on A:A so that every instance of "X/Y", ...
- 1
0
votes
1
answer
36
views
Excel/GoogleSheets Forumla to List All Rows Without A Corresponding Value in Another Column
I'm hoping there is a simple way to perform the following. I have 2 columns (A,B), and I would like a third column (C) which consists of all values from column A that have an empty cell in column B. I ...
0
votes
1
answer
750
views
Formula to identify a list of codes in various combinations
Bit of a tough one to explain, but here we go... I have a large database (15K rows) of unique codes (AA1, AA2, AB1, etc.) in letter & numbers convention that identify them to a specific name (in ...
0
votes
1
answer
739
views
Count cells in a column between values until specific value reached in Google Sheets - Multiple Criteria
I am trying to do a tricky conditional count on a dataset. I need to count the number of rows that meet a set of criteria (after a specific date, a certain category) that sums up to a set limit - that ...
0
votes
2
answers
222
views
Perform multiple calculations in one cell
I have a spreadsheet that has 4 columns that perform a simple "if" based on some data entered elsewhere. Adjacent to each of those columns is another column that assigns a "1" or &...
0
votes
1
answer
213
views
In Excel I am wondering how if a value is met in a column then to sum up the cell to the left of that value across multiple rows if that makes sense
For example where the cell says David I want to sum all the cells on the left of David in £ up to a total like at the bottom not sure how I'd go about this.
£4500 IWI 14/12 DAVID
...
- 1
0
votes
2
answers
453
views
Extracting multiple values from a sentence in single cell with same preceding value. eg. Player 1, Player 2
Im trying to extract just the Player numbers from the sheet below.
What is the best way to extract just "Player + (player number)" ?
- 3
0
votes
1
answer
24
views
Need help in Excel Adding 2 columns
i just want to add column 2 to column 1 if and only if column 2 has just 1 text character in its cell .enter image description here
- 1
0
votes
1
answer
59
views
How to FIlter/Match spreadsheet data based on two row headers?
I need to find a value based on a specific column and header, that also fall within another merged header (spans 3 cells) like the example below:
|January 2020 |February 2020 |...
- 259
0
votes
1
answer
154
views
How can you format each row from a pivot table to display transposed with the headers repeating themselves?
From a pivot table, I am attempting to take each row of data and format it transposed with the headers from the pivot table present in each instance. Here is a link to a Google Sheet that has a sample ...
0
votes
0
answers
246
views
How to bring sum of previous column in to next column automatically, in Excel or Google Sheets?
I'm new to this. I'm trying to make a personal budget sheet, where I can bring the previous months' sum into the next month's column as "savings from last month". Basically I want to show ...
- 205
1
vote
0
answers
65
views
Formula - Get previous date and sum of unique months based on unique user
Goal Estimate the next date Abbie will make the purchase based on average days.
Screenshot to give a better idea:
Questions
A.1 & A.2 - how to search for the most recent previous purchase date ...
- 11
2
votes
1
answer
1k
views
CONCATENATE cells in a column IF condition in other colum is met
Using Google sheets (or Excel if Google sheets can't do it). I'm trying to concatenate any cell in B column, if its corresponding A cell is "include". The columns are very long and therefore ...
- 25
1
vote
1
answer
47
views
Excel/Sheets: Average only the input data in a range of data?
I've got an annual budgeting spreadsheet in which each month is listed along with given categories of items. I'm trying to find my average monthly expenditure on any given category, but don't know the ...
0
votes
0
answers
22
views
Listing function points in a spreadsheet/excel [duplicate]
I am working on a Pre-Calculus Polar coordinate graphing project that incorporates some creativity; My current plan has me graphing the Nautilus Shell as my focal point.
However, I have to list points ...
0
votes
0
answers
805
views
How to apply Text cell format permanently on a google sheet's column?
I recently added IDs as Number in a column those are basically built as alphanumeric.
Then I've applied Text cell format on those IDs to keep their data consistent with the other cells in that column, ...
- 21
1
vote
1
answer
449
views
How to copy rows of specific columns based on a criteria (and then continuously update)?
I have 2 sheets, let's call them sheet 1 and sheet 2.
In sheet 1, I have some data where essentially at its core its a Scoring tally, something like this:
Name
Age
Gender
Score
Jack
12
Male
155
...
1
vote
1
answer
286
views
How to calculate max sum held over a month?
I need help with a formula that needs to calculate the max sum held at one time for each month.
In this example it would be 54 for March (12+13+14+15, held on the 29/03/2021). Month is determined by &...
- 23
1
vote
1
answer
392
views
Extracting text between two repeated sets of values in Excel
I have campaign names that conform to the following structure:
[Network] - [Language] - [Country] - [Channel] - [Campaign Type] - [Free Text]
This could look like:
Google - English - India - Display -...
- 11
0
votes
1
answer
127
views
How can you show the first cell in a cluster in Excel? And how can you lookup the value?
Several questions. But let me break down the problem of what I am trying to do:
I have a condition to show YES/NO if a certain value is met. If YES >= 60 and NO <=30 - this is easy I can do ...
- 1
1
vote
2
answers
228
views
Is it possible to reuse the same sumif range?
In the following table, I constantly reevaluate a2:a4 to run different calculations against it. In a real table it would have been thousands of rows like it.
Is there a way to somehow store a2:a4 in a ...
- 1,008
0
votes
3
answers
148
views
Sum of vlookups for each value in a row
There should be a way to do this, and yet I'm struggling...
==The Data==
Sheet1
A B
+---------------+-------+
| Fishy_Product | Price |
+---------------+-------+
| Cod | $9....
- 3
0
votes
1
answer
476
views
Can I make a graph for iterative function in MS Excel or Google Spreadsheets?
Let's say I have a formula:
k = 0.2
a = pow(a, k)
I'd like to see how the curve changes on each iteration with a different k value. The formula can be anything. The only thing is - I need the ...
- 193
1
vote
1
answer
15k
views
google sheet formula, if “yes”, copy to other sheet
I’m trying to figure out the formula in google sheets - if the cell is “yes”, then copy the adjacent cell to another sheet. Can anyone help?
- 13
0
votes
2
answers
404
views
Return text string for highest duplicate values
I've been battling with a google sheets conundrum so hoping someone can help.
In a nutshell, I'm trying to achieve the following based on the image below:
Where the name is the same in column C
...
- 25
0
votes
1
answer
153
views
Excel/Sheets - Adding the content of a row to the end of another?
So I'm still new-ish to data analysis on Excel and/or Sheets, and I'm trying to "merge" data. Essentially, I have a few thousands rows, and I want every second row to be added to the first. ...
- 3
0
votes
1
answer
68
views
Populate names in cell based on tiered choices using excel formula
I have 2 tables
First table is a list of items and 3 other columns depicting choice tiers
Items Choice 1 Choice 2 Choice 3
item1
item2
item3
item4
...
- 1
0
votes
2
answers
41
views
Function that writes name depending on two conditions
I would like to know how to implement this in an excel formula: I have a column with some names, a row with different numbers (dates), and the remaining values work as "keys". For instance:
...
- 1
2
votes
1
answer
534
views
Update an excel list dynamically without using scripts
I'm a bit stuck and could use some help.
I am trying to keep a list in excel updated dynamically, based on conditions in other cells. Something along the lines of (IF both cell values in A1 and A2 ...
- 25
0
votes
3
answers
1k
views
How to highlight a row if "yes" appears more than once across multiple columns
I have a product list export that shows the products and which categories those products are assigned to.
I want to audit the product list by highlight products that are assigned to multiple ...
