People who code: we want your input. Take the Survey

Questions tagged [spreadsheet]

Questions about spreadsheet software in general and interoperability between spreadsheet programs.

Filter by
Sorted by
Tagged with
0
votes
1answer
17 views

Excel - sort one spreadsheet by exact order of second spreadsheet based on one column with same data

I trying to find a way how to sort one spreadsheet into the same order like second spreadsheet - based on their one same column for example. Here's an example what i need, i will make more sense like ...
0
votes
0answers
15 views

Excel on notebook

I want to change all numbers in column I to be - (negative) if the corresponding words in column B are SELL Example Col B row 1 “sell” then col I row 1 -123 Col B row 2 “buy” the col I no change Col B ...
1
vote
1answer
15 views

Combine one set of time series data with another when some dates are missing in one data set

I have a LibreOffice Calc spreadsheet which contains two sets of time series data e.g. |Date-A |A | | | |Date-B |B | |2020-01-01|101| | | |2020-01-01|201| |2020-01-02|102| | | |2020-01-02|202| ...
1
vote
2answers
44 views

Excel: How can I find all the occurrences of the matching columns

Consider I have the following table (unsorted): Name ID Account John Doe 1 1234 John Doe 1 6524 Jane Doe 2 6346 Jane Doe 2 1241 Jane Doe 2 3634 And another one like this: Name Account 1 ...
0
votes
1answer
45 views

i want to return value( eg;- sum) only if the source is not blank,other wise return blank !!not zero!! in excel, wps

see this screenshot for reference see this screenshot.. I used SUM formula in the botton cells to get sum of all values from each row , the bottom cells display TOTAL and i will assign the TOTAL ...
0
votes
1answer
308 views

Hyperlink to certain text in excel page?

I'm trying to create hyperlinks to certain points in a large data sheet. Each are separated by a block of text identifying what is there. I know I could create the hyperlink to certain cells in the ...
0
votes
1answer
25 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 ...
0
votes
1answer
26 views

In LibreOffice Calc, why would sort buttons and menu items suddenly become disabled?

I was creating a spreadsheet in LibreOffice Calc. Several times, I used the "Sort Ascending" and "Sort Descending" toolbar buttons. All was well, and everything was working as ...
0
votes
1answer
32 views

How to get LibreOffice Calc to recognize dates in YYYY/MM/DD format?

I've been given a large tab-separated-values (TSV) text file that contains dates in the format YYYY/MM/DD. I tried copy and pasting the dates into LibreOffice Calc, but it didn't recognize the strings ...
1
vote
1answer
35 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 &...
1
vote
2answers
33 views

Need to simplify date entry in Excel spreadsheet

Every month I need to do a large spreadsheet for billing purposes. Among other columns I have one start date and another for end date, formatted as dd-mmm. To enter this date in the spreadsheet for ...
1
vote
0answers
8 views

Searching indexed spreadsheets from Windows 10 explorer “search”

I have a client that used to use Windows 7, and was able to search directly from the Windows explorer (start menu) to find spreadsheets by searching a number that was on column B within. Now that they'...
0
votes
1answer
45 views

Based on cell formula, display another cell value

I want Excel to look into the formula of a cell and then display a different cell based on that. confused yet? Column A on sheet 1 has a size and column B on sheet 1 has a tolerance. Could be same ...
0
votes
3answers
116 views

Excel MAX IF Function To Ingore Zero Date (1/0/00)

=MAX(IF('[YTD -2021.xlsx]OB'!$H$2:$H$23967=$C5,IF('[YTD -2021.xlsx]OB'!$J$2:$J$23967=$B$1,'[YTD -2021.xlsx]OB'!$A$2:$A$23967))) I want to ignore Zero date and result should give me 0 or blank instead ...
1
vote
1answer
41 views

Sum and lookup over range in spreadsheet

I have a google spreadsheet that looks like this Items Item Value A 10 B 12 C 15 D 20 Calc Total Items A A B A A C C C In Total I want to have the a formula that sums the value of the ...
0
votes
3answers
50 views

Distinguish between A* and A in excel

I’m creating a spreadsheet to be used in schools that use various countifs, subtotals etc to look up data. A big problem I have is that I need to distinguish and count separately students on an A* (A ...
0
votes
3answers
63 views

Is there a way to define a range with infinite row or column number in a spreadsheet program?

When defining a named range in a spreadsheet program, is there a way to define a range that covers for example A1:A[*infinity*], so I will be sure that the range will always include all rows, whether ...
0
votes
1answer
41 views

How to group multiple date|data columns to one single date column on the left?

I've got a large set of data that looks like: and I've been trying to format it so that instead of having a column of dates for each item, there's only one column of dates on the left. The problem is ...
0
votes
0answers
29 views

How to isolate list of values in Excel spreadsheet

I have a randomly generated list of 5,000 filenames. I'd like to isolate this list from a seperate excel spreadsheet of 60,000 rows, in order to extract the respective values from other columns. How ...
0
votes
0answers
12 views

How to return the sheet name of a given cell of a query result

I have scrubbed the internet for answer to this and and either I am asking the question the wrong way or it isn't possible. Here is a example template of what I am wondering about: https://docs.google....
0
votes
1answer
15 views

Count number of rows with a specific MONTH in the date of a specific cell and a boolean condition in another cell

Let's say I have a number of dates and whether they occupy full or half a day... DATE | WHAT | HALF? ... 2021-4-1 | Something Something | false 2021-4-2 | Something Something | ...
3
votes
1answer
52 views

Filtering over merged downwards cells in MS Excel-2016

Note: This question has been asked back in 2010 on this site here. And yet, I would like to post it one more time, showing what I did and what didn't work. Besides, Excel has changed quite a bit since ...
0
votes
1answer
31 views

How to highlight down columns for many columns at once that are separated in an excel sheet?

I have 30 columns of data with over 10,000 rows. I'm using Ctrl+Click to highlight specific cells and then use Ctrl+Shift+Down arrow to highlight to the bottom, but I noticed it won't let me do that ...
0
votes
0answers
26 views

In a LibreOffice Calc (or OpenOffice Calc) spreadsheet, how can you maintain multiple groups of columnar data?

I have a LibreOffice Calc spreadsheet that contains 3 sets of data. It is organized like this: SUBJECT A SUBJECT B SUBJECT C Date X Y Date X Y Date X Y Date X Y ...
0
votes
0answers
24 views

Combining multiple data entries, for some unique and some duplicate customers. (100K Rows, 30 Columns)

(Top Table)-I need to remove the duplicate customers (left column) while filtering/move/pasting all of the other data in the columns to the right to move upward to complete it across 30 columns.- ...
0
votes
3answers
53 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....
0
votes
1answer
26 views

In LibreOffice Calc, is it possible to use a complex function and a data range as arguments for a MIN() function?

I'm going to simplify a bit to hopefully get at the crux of my question. I have a spreadsheet in which a cell has a massive formula. (Again, I'm simplifying to avoid a question that is so long no one ...
0
votes
1answer
68 views

Too Many Arguments when using IF google sheet

I want to implement this formula in Google sheet and I'm getting the error for too many ifs. Any help? =IF(I2="win",SUM(J2*G2),IF(I2="loss",SUM(J2*-1),IF(I2="push",J2),IF(...
0
votes
1answer
79 views

Libreoffice Calc : Fill in a range of cells with a known start and end value. Automatically

I have two cells separated by some empty cells. Values in parentheses. A1 [ 23 ] A23 [ 433 ] I want the spreadsheet to figure out the best way to increment the values between them, based on how many ...
0
votes
2answers
22 views

Making formula work only when adding to a value

I am trying to have a number automatically update itself on our inventory spreadsheet (since we seem to forget to do so ourselves)...but I can't seem to get it quite right. So our inventory process ...
0
votes
1answer
87 views

What is the processed called when converting data in repeated columns to rows on a spreadsheet?

If I have a spreadsheet with a data structure where similar information is repeated across multiple columns like so: TxID Date Merchant Item 1 Name Item 1 Value Item 2 Name Item 2 ...
0
votes
0answers
25 views

How do I keep just the first row of each unique occurence of a value in a column in LibreOffice Calc?

I have a sheet in Calc that looks like this: A B C D E 1 Call # Title Barcode Status Check-out date 2 070.49 GUIB The ...
0
votes
1answer
42 views

Excel Change value of each cell with a value in row to count+1 of cells with values in row

I have the following spreadsheet: I have 57 rows and ~750 columns, in some cells I will have the number one. I want to change the cells with one to a rolling count for each row. I have shown what I ...
0
votes
1answer
54 views

How to fix a messed Excel sheet and make a proper format?

I have a problem in a specific sheet: I need only country,network,NetworkName and currency columns, but in sheet all messed up. As you can see in network Colum there are name and MCCMNC code also same ...
0
votes
0answers
28 views

Sorting an excel column with numbers and keep track of cell indices

So, this is my problem, I have two excel columns like : 1 // 0.5 2 // 3.3 3 // 1.2 I would like to sort the second column in ascending(or descending) order, and also change the first column as ...
2
votes
1answer
48 views

How to get the total sum of counted values separated by commas in multiple rows, but disregarding zeroes?

I want to count the number of values in multiple rows separated by commas--excluding those that have 0 values, using the following formula: =SUM((LEN(SUBSTITUTE(C30:H30&",",",0"...
0
votes
0answers
43 views

Fill down every (x) rows for (x) rows inserting as new rows instead of overwriting what's below in spreadsheet (WPS Office)

I have an existing CSV which contains shipping rates for every country in the world. I need to extend the rates for each country. Each country currently occupies 80 rows each. Every 80th row (the last ...
2
votes
1answer
62 views

Merging different number of rows with some common data

I have a data like this in an excel sheet: Rensi 06/10/2020 Rensi 07/10/2020 Rensi 08/10/2020 Sam 06/10/2020 Sam 08/10/2020 Mathew 07/10/2020 I want to ...
0
votes
1answer
637 views

Excel: How to show a floating text box only when I select the cell?

There's a type of text box in an Excel file that is associated with a cell and appears only when you select that cell uniquely (e.g., by clicking on it or moving to it with cursor keys).  Hovering ...
0
votes
3answers
219 views

Is there an option or setting that prevents VLOOKUP from returning zero when it finds blank cells

Is there an option or setting that prevents VLOOKUP from returning zero when it finds blank cells or does not find the lookup value. I do not want to suppress the zeros by formating them out. I want ...
0
votes
1answer
41 views

How do I assign a name to a value in excel?

How do I assign a value to a name? For example If the word "WHOLE" is in Column I it will count as "1", and if the word "HALF" is in column I that it will count as "....
0
votes
0answers
22 views

How to get a conditional scale with with 4+ colours without VBA?

How do I achieve a conditional scale with with 4+ colours without VBA? This is what I am trying to achieve: The only way I know to achieve the above is by using two separate scales which is very ...
0
votes
1answer
29 views

XLS: 'if statement' calculation

I have a cell B19 which consists of taking the value of another cell and dividing it by 5: =B15/5 However I'd like to change it so that it only divides it by 5 if the B15 is more than 1000... and for ...
0
votes
2answers
30 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: ...
0
votes
1answer
77 views

Why does conditional formating “white out” only certain manual formating is some cells?

I'm trying to make a spreadsheet with tables, with some automation, that keeps track of my work hours. (Note I'm not using the Excel "format as table" feature. This is all by hand). ...
1
vote
1answer
383 views

How do I fill a row based on the number of filled columns in the row above?

Here's my current spreadsheet: Rows 1 and 3 are the input, and I want to fill row 4 automatically based on how many columns there are in row 3. Example end result: The "discount factor" is ...
0
votes
2answers
55 views

How to write an Excel Formula that takes a total across multiple repeating rows [duplicate]

I'm working with payroll data in a spreadsheet. What I'm looking to find is a total across different rows of employees with different roles. For example, how could I write a forumla for the "...
0
votes
1answer
45 views

Sumifs function in excel

Hi my SUMIFS function is not adding up figures at all. The SUMIFS formula I use is below: =SUMIFS($O:$O,$A:$A,"distribution - reinv",$D:$D,">="&DATE(1,5,2020),$D:$D,"&...
0
votes
1answer
41 views

Excel: how to sort rows by same column values

I want to sort (keep) rows by the same column values. For example, As you can see above, I want to sort the rows by the same (matched) column values of code1 and code2 as follows: Basically, I have ...
0
votes
1answer
28 views

Excel: Selective duplicates removal

I want to remove some duplicates, but not all. Let me give you an example below: A B UK James UK Coco UK Lydia UK Matthew US Max US Kim US Anna FR Marie FR Lin I want to turn the columns ...

1
2 3 4 5
13