Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 13 Aug 2019 18:09:24 +0200cCalc How do I match name and count all cells with corresponding namehttps://ask.libreoffice.org/en/question/204525/ccalc-how-do-i-match-name-and-count-all-cells-with-corresponding-name/ Please see attachment
Trying to match names and then count numerical value for each name for cumulative total
example: column A, Row 2: name "A",
How do I match A2 name "A", to column AN, then add up all the numbers in column AM that match A2 name "A" and display in B2, I have added all the correct data in DA:D9, so the user is able to verify formula : : : ; .. .. [C:\fakepath\LO Matching trying to find accumulative count 08-13-2019.ods](/upfiles/15657125013793182.ods).
Thank you in advanceJG101Tue, 13 Aug 2019 18:09:24 +0200https://ask.libreoffice.org/en/question/204525/Getting #N/A with INDEX and MATCHhttps://ask.libreoffice.org/en/question/204123/getting-na-with-index-and-match/I'm using that spreadsheet as a checklist and for ease of use I wanted to check through multiple ranges whether or not a "Yes" or "No" was displayed.
I have four cells containing the following formulae:
S4: <code>=INDEX($D$4:$D$64,MATCH(R4,$B$4:$B$64,0))</code>
T4: <code>=INDEX($h$4:$h$64,MATCH(R4,$f$4:$f$64,0))</code>
U4: <code>=INDEX($l$4:$l$64,MATCH(R4,$j$4:$j$64,0))</code>
V4: <code>=INDEX($p$4:$p$64,MATCH(R4,$n$4:$n$64,0))</code>
As expected cell T4 says "No" while the others say "#N/A"
My issue is with cell W4.
With that formula I get a "No":
<code>=IFS(</code>
<code>NOT(ISNA(S4)),S4,</code>
<code>NOT(ISNA(T4)),T4,</code>
<code>NOT(ISNA(U4)),U4,</code>
<code>NOT(ISNA(V4)),V4</code>
<code>)</code>
Plain and simple I'm displaying the first cell isn't saying "#N/A". It works as expected.
But with that formulae I get a "#N/A":
<code>=IFS(</code>
<code>NOT(ISNA(INDEX($D$4:$D$64,MATCH(R4,$B$4:$B$64,0)))),INDEX($D$4:$D$64,MATCH(R4,$B$4:$B$64,0)),</code>
<code>NOT(ISNA(INDEX($H$4:$H$64,MATCH(R4,$F$4:$F$64,0)))),INDEX($H$4:$H$64,MATCH(R4,$F$4:$F$64,0)),</code>
<code>NOT(ISNA(INDEX($L$4:$L$64,MATCH(R4,$J$4:$J$64,0)))),INDEX($L$4:$L$64,MATCH(R4,$J$4:$J$64,0)),</code>
<code>NOT(ISNA(INDEX($P$4:$P$64,MATCH(R4,$N$4:$N$64,0)))),INDEX($P$4:$P$64,MATCH(R4,$N$4:$N$64,0))</code>
<code>)</code>
Is this a bug or am I missing something here? All I did was to replace the cell references with their content.
I know that final formula could be simplified to remove some the INDEX but that doesn't solve the issue. And finally I tried to format things a bit to make the formulae easier to read.HuillamSat, 10 Aug 2019 12:47:45 +0200https://ask.libreoffice.org/en/question/204123/Pull data from long-form schedule to single day sheethttps://ask.libreoffice.org/en/question/203415/pull-data-from-long-form-schedule-to-single-day-sheet/I am desperate for help and am far out into the deep end without my water-wings.
I have a schedule that is in a long form and need to generate a daily post-able schedule from that, with a pre-designated layout, which I cannot change. I cannot restructure either sheet, as doing so is well above my pay-grade. I have been given a 'Make it work" assignment, and am drowning.
Sheet1 has the names in one column, and the full schedule for the month laid out to the right, each column being a day of the month, and the cell values being the work area assigned that day. The values-0p90 entered into Sheet 1 daily columns are values 1-6, L (Lead), and D_1-6/1-6 (For double shifts assigned, the _ being replaced with an E, N, or A, depending on the normal shift worked by the person and the second shift they will be working.)
I need to pull the names from Sheet1 (Entire Month) into cells in sheet2 (Daily printed schedule), based on the values in the day assignment columns to the right of the names on Sheet1.
On Sheet2 I have a column that lists the designated work areas. I need to pull the name for the person scheduled for that area into Sheet2, based on what they are assigned on Sheet1 for that day, based on which column to look at as selected via a Cell on Sheet2, such as Day3 or just 3, which corresponds to the day# of the column on Sheet1 that needs to be looked at to pull the names from the static name column on Sheet1.
I was able to determine the correct column number, using a MATCH(), but don't know to do the rest of the manipulation/lookups needed to pull what I need. ALL suggestions are welcomed and appreciated
Note: Due to security setups, I cannot use Macro's for this, it has to be done with some kind of formula, even if that is pulled into another cell set and then repopulated into where I need. I can have hidden sheets and cells. Hope that helps.[C:\fakepath\Schedule_V1_scrubbed.ods](/upfiles/1564950814491319.ods)D.C.BallardSun, 04 Aug 2019 21:56:16 +0200https://ask.libreoffice.org/en/question/203415/"match" (in CALC Version: 6.2.2.2 (x64)) is not working right...https://ask.libreoffice.org/en/question/202350/match-in-calc-version-6222-x64-is-not-working-right/ I am building a rather large and complex "design project management: (Calc) spreadsheet. The user selects a "Design Type" using a "validity" command. Elsewhere I use the "Match" command to get the "offset" from the validity's "source text." It does not always work.
![image description](/upfiles/15641611815012754.png)
This is the "source for the "validity" and associated data.
![image description](/upfiles/15641612576421365.png)
This is the "result" of my "match" and "offset" efforts.
The "Match" command is written as: "=MATCH(AMG6,$'Operational Data'.$AMC$3:$AMC$13)" in my code. ???Lew MerrickFri, 26 Jul 2019 19:17:57 +0200https://ask.libreoffice.org/en/question/202350/How build 4th "target" col based on earlier values compared to 3 cols?https://ask.libreoffice.org/en/question/202040/how-build-4th-target-col-based-on-earlier-values-compared-to-3-cols/ I want to generate a "target" column based upon values in another 3 columns.
The existing completed columns are "driver", "Abr1", "drive2".
Where "driver" and "drive2" are the same, use "Abr1" value in "target".
Where they differ, select the value in earlier "Abr1" that corresponds to "drive2" in the same row.
In my hand constructed "target" here, the first 3 rows are identical to "Abr1" because "driver" and "drive2" match.
**Then the algorithm uses the value in "drive2" to select a prior corresponding value in "Abr1" for "target" down the column.**
Here are the columns:
image:!(/home/jim/Downloads/Pajek_Data/Screenshot_20190723_052400.jpeg "example")
I have had *interesting* experiences trying VLOOKUP, INDEX with MATCH, etc. but no success yet. I am resisting writing a custom program as I prefer a standard Calc method I can adapt for future similar tasks.dataguyTue, 23 Jul 2019 15:02:13 +0200https://ask.libreoffice.org/en/question/202040/How to use MATCH and INDEX with multiple columnshttps://ask.libreoffice.org/en/question/197869/how-to-use-match-and-index-with-multiple-columns/[In the attached file](/upfiles/15607597117464874.ods), I have a database with three columns: year, quarter, and revenue.
I want to write a formula that takes as input the year and the quarter, and returns the revenue.
Using MATCH and INDEX, I could query either the year or the quarter - but I need both together.
Can you help me fix the formula?Erel Segal-HaleviMon, 17 Jun 2019 10:23:38 +0200https://ask.libreoffice.org/en/question/197869/Multiple column match, and seek multiple output values from one column.https://ask.libreoffice.org/en/question/195479/multiple-column-match-and-seek-multiple-output-values-from-one-column/*Please see the attached ods file with two sheets for the problem.*
The problem is, when a name is selected in dropdown at B17 : [C:\fakepath\ANLYS.ods](/upfiles/1559121453460645.ods).
Scenario 1:
To have all the corresponding STRIKE_PR of that particular name filled up in the column D beginning at D17 after matching OPTSTK, CE, 30-May-2019.
Scenario 2:
To have only STRIKE_PR of top three highest OPEN_INT corresponding to that particular name. filled up in the column L beginning at L17 after matching OPTSTK, CE, 30-May-2019. (OPEN_INT are in column H in yday sheet)
As you can see, I tried with INDEX and MATCH function, but it is showing only the first matched STRIKE_PR ignoring the rest. Perhaps it requires a different combination of functions.
I hope someone can help / guide me.
thanksSaiWed, 29 May 2019 11:25:39 +0200https://ask.libreoffice.org/en/question/195479/Aligning two lists in Calc problem fixed by Gilbertohttps://ask.libreoffice.org/en/question/193334/aligning-two-lists-in-calc-problem-fixed-by-gilberto/Hi, I have two lists from different sources that I need to make sure that the contents of the lists are positioned in the same place within their cell.
[C:\fakepath\street names.ods](/upfiles/15574704967903501.ods)
In the above example, I need the contents "Green Cells" to be positioned in exactly the same position within their cell as the contents of the "Yellow Cells".
I have attempted to add or subtract spaces but to no avail.
What I am attempting to do is use Matching formula
"=MATCH(E2&D2,$'Mailman''s Route'.A$1:A$538&$'Mailman''s Route'.B$1:B$538,0)" where the "Green Cells" are E and the "Yellow Cells" are $'Mailman's Route' .A$1:A$538.
The other part of formula works, if I cut and paste from "E" into "A$" and I can get a Match to work without doing anything to the "D" and "B$" cells.
The Yellow List is taken from a database and arrives to me an Excel Sheet that I open in Libre Calc
As I have 16 Green List and up to 33 Yellow Lists per Green Lists I really need to automate.
I have done the cut and past thing as a workaround but I would like to automate the process as I do not have the time to do cutting and pasting.
Thanks
Stuartstuarts.burgersFri, 10 May 2019 09:12:20 +0200https://ask.libreoffice.org/en/question/193334/Problem with =Match in Libre Calchttps://ask.libreoffice.org/en/question/191037/problem-with-match-in-libre-calc/ I am using the formula "=MATCH(E2&D2,'Mailman''s Route'.A$2:A$500&'Mailman''s Route'.B$2:B$500,0)”
Where Mailmans route is a list of Street Names in Colum A and Street Numbers in Colum B
If I cut and paste the Street Name into Colum A from the data set that I wish to find matches in then everything works perfectly. If I type the street names into Colum A then some streets will do a match and others not
![Mailmans Route](/upfiles/15557198286386664.png)
In the above example, both Glastonbury Drive and Quantock Place are in the data set only Glastonbury gets matched
with Quantock not being matched. I have cut and paste Quantock for Quantock 2 below and only then it will match (see below)
![Cut and Paste Mailmans Route ](/upfiles/15557205814596919.png)
Quantock 2 will match but Quantock 4 etc will not
This has me stumped can a nice person help
Thanks Stuart
stuarts.burgersSat, 20 Apr 2019 02:46:45 +0200https://ask.libreoffice.org/en/question/191037/Index Aggregate formula. Need expert help.https://ask.libreoffice.org/en/question/187518/index-aggregate-formula-need-expert-help/I hope someone can help me out with this..
The formula (listed below)works great in Excel but in Calc I get $Div/0! Errors. I think the problem is with the "aggregate" portion.
Here is the formula:
=IF(ROWS($AH$4:AH4)<=$AH$3,INDEX($S$4:$S$16,AGGREGATE(15,3,($R$4:$R$16=$AI$3)/($R$4:$R$16=$AI$3)*(ROW($R$4:$R$16)-ROW($R$3)),ROWS($AH$4:AH4))),"")
A shortened version of the spreadsheet is attached for download.
The picture illustrates what I'm trying to accomplish. I need to be able to change the value in AI3 (currently "3") and have the list update accordingly. Open in Excel and it works great. Open in Calc and I get errors. I need this for work and I only have Calc available there. Any help is very much appreciated!
[C:\fakepath\PARTFINDER.ods](/upfiles/15530300075189608.ods)
![image description](/upfiles/15530274481726606.jpg)kevingrg77Tue, 19 Mar 2019 21:19:04 +0100https://ask.libreoffice.org/en/question/187518/Calc: Match closest text and align extended resultshttps://ask.libreoffice.org/en/question/181899/calc-match-closest-text-and-align-extended-results/I'm attempting to convert from one db to another. In both db's is a **title** and an associated **id** among other fields. The ids of course are different between the two and I need to map one to the other. While the bulk of the titles are exact matches, not all are.
I've tried using Match and Vlookup for exact matches and have gotten ok results but I can't figure out how to align the results. Once I figure that out I could test some regex for my match criteria to maybe catch a few more or just hand edit the rest. (1166 rows in all)
So in the most basic form is:
________________________________________
|db1 Title |db1 id| |db2 Title|db2 id|
----------------------------------------
1|Title |45 | |Title #4 |64 |
2|Title f |24 | |Titlex |1125 |
3|Title #4 |59 | |Title |72 |
4|Title (?) |2 | |Title f |48 |
I'm trying to end up with title and both corresponding ids on the same row and deal with the non matches (#N/A) by hand or regex matching. Is this possible?akoviaTue, 05 Feb 2019 01:34:33 +0100https://ask.libreoffice.org/en/question/181899/LibreOffice Calc - Partial Match Column Compare Questionhttps://ask.libreoffice.org/en/question/177867/libreoffice-calc-partial-match-column-compare-question/ How can I perform a case insensitive partial match from one column to the next? Example.
Column A
green
red
brown
Column B
The green tree
Orange Leaves
Brown Dirt
I would like to search all the phrases in Column B with the words in Column A.
Desired Outcome:
The Phrase "The green tree" in column B should be identified because the word "green" from column A was found.
The Phrase "Brown Dirt" in column B should be identified because the word "brown" from column A was found.
I've scoured the web looking for a solution and I haven't found one that can be easily used without the knowledge of writing code.
IvanWatkinsMon, 31 Dec 2018 22:49:54 +0100https://ask.libreoffice.org/en/question/177867/Lookup() vs. Index() of Match()https://ask.libreoffice.org/en/question/176135/lookup-vs-index-of-match/I have a spreadsheet in LibreOffice Calc 6.1.2.1. I have a column which had this formula:
=LOOKUP(D2,$J2:$Q2,$J$1:$Q$1)
It gave appropriate answers for about 2/3 of the cells, but gave #N/A for the remaining 3rd.
I replaced the column with this formula:
=INDEX($J$1:$Q$1,1,MATCH(D2,$J2:$Q2,0))
That gave me just what I wanted. However, I still don't understand why these formulas are different. Shouldn't they have the same results? Does this have something to do with sorting? Documentation for the [lookup function here](https://help.libreoffice.org/6.1/en-US/text/scalc/01/04060109.html?&DbPAR=CALC&System=UNIX#hd_id3159273) didn't help me.sondrakThu, 13 Dec 2018 16:05:49 +0100https://ask.libreoffice.org/en/question/176135/Hello! On calc, how do I count the cells of range "X" that contain the exact data with cells in range "Y"?https://ask.libreoffice.org/en/question/175477/hello-on-calc-how-do-i-count-the-cells-of-range-x-that-contain-the-exact-data-with-cells-in-range-y/So, I have two columns that I want to compare for matching data (numbers or text) and I want to count the duplicates. Like 5 cells from column A have the same data with cells in column B.
Thank you
YanisyanismwaSat, 08 Dec 2018 00:30:14 +0100https://ask.libreoffice.org/en/question/175477/find value in matrix (in other words: find value based on 2 criteria)https://ask.libreoffice.org/en/question/167993/find-value-in-matrix-in-other-words-find-value-based-on-2-criteria/Hi, i am trying to make <s>excel</s> Calc find a value in a table i have in the same spreadsheet, that matches 2 conditions.
I am trying to make Calc return the "maximum load value" of musical instrument strings based on "diameter" and "type"
My table looks like this:
diameter | type | max. load
0,7_____________1_____________x
0,75____________2_____________x
0,8_____________1_____________x
etc.
The data, consisting of "max load values" looks like this:
diameter | type 1 | type 2 | type 3
0,7_____________500____________600____750
0,75____________550____________660____820
0,8_____________600____________710____870
etc.
how do i find "x"?Lander123Sun, 07 Oct 2018 15:01:29 +0200https://ask.libreoffice.org/en/question/167993/Match Cells conditional format background.https://ask.libreoffice.org/en/question/165840/match-cells-conditional-format-background/Please need help with Match Cells conditional format, please see attached file for example
:
How do I color background in G23 when it matches EXACT number in ROW M8:M17
[C:\fakepath\Match Conditional Formating Match Cell 09-16-2018.ods](/upfiles/15371187698905175.ods) JG101Sun, 16 Sep 2018 19:28:19 +0200https://ask.libreoffice.org/en/question/165840/Loops and Matching: Combine cells from multiple sheets into one.https://ask.libreoffice.org/en/question/164182/loops-and-matching-combine-cells-from-multiple-sheets-into-one/ **Example:**
- Sheet1 contains columns: **SKU DATE**
- Sheet2 contains columns: **SKU ASIN**
- Sheet3 contains columns: **ASIN COST**
- Sheet4 contains columns: **SKU DATE COST**
------
Sheet4 will populate data from Sheet1, Sheet2, Sheet3.
------
Sheet4:
- populate SKU and DATE column from Sheet1
- loop through Sheet4 SKU column, match SKU in Sheet2, find corresponding ASIN
- loop through Sheet3 ASIN column, copy corresponding COST to Sheet4ph0ttWed, 29 Aug 2018 17:19:44 +0200https://ask.libreoffice.org/en/question/164182/I'm using the 'match ' command, column of numbers goes from negative to positive, match fails across the transition.https://ask.libreoffice.org/en/question/162794/im-using-the-match-command-column-of-numbers-goes-from-negative-to-positive-match-fails-across-the-transition/I'm using the 'match ' command, column of numbers goes from negative to positive, match fails across the transition.
Is there a workaround for this?
Column of numbers like this, as it tries to look past the boundary where the numbers go from negative to positive, it fails past that point, and gives a #N/A :
-0.31288
-0.25805
-0.20773
-0.16138
-0.11853
-0.07879
-0.04184
-0.00737
0.02485
0.05504
0.08339
0.11006
0.13521
0.15896
mlytle0Mon, 13 Aug 2018 17:25:15 +0200https://ask.libreoffice.org/en/question/162794/Match cannot find number in formula cell but okay in value cellhttps://ask.libreoffice.org/en/question/154717/match-cannot-find-number-in-formula-cell-but-okay-in-value-cell/ Attached is a screen shot of results.
![image description](/upfiles/15261316455335244.png)
The issue comes with the 2nd and 3rd columns (both starting with 0.911030427)
The 2nd column have formula in their cells.
The 3rd column below the row with 3756 in it, is a cut and paste of the 2nd column text, numbers etc.. only, no formula.
0.911030427 in both cases are calculated from =MAX(AK11:AK14717) and 1 column removed.
The 14707 numbers are calculated by using =MATCH(AK2,AK11:AK14717) and the row with image-????? in them is =INDIRECT("A"&(AK3+10)). The tables themselves are a mixture of formulas and spaces e.g. =IF(ISBLANK($J11),"", AK11) etc..
As you can see, the maximum calculated is correct but the 2nd column cannot find where the 0.911030427 is and returns the last line of the table.
Another spreadsheet like this does not show any similar problems. Just this 2nd spreadsheet in the file.
Is this a known bug?
(I am using Version:5.1.6.2 Build ID: 1:5.1.6-rc2-0ubuntu1-xenial3)Daniel WongSat, 12 May 2018 15:45:40 +0200https://ask.libreoffice.org/en/question/154717/I am trying to match the last non blank cell in a row with the header above it. I used this formula in Excel, =LOOKUP(2,1/(L2:R2<>""),M$1:S$1) but its not working in LibreOffice. Any suggestions?https://ask.libreoffice.org/en/question/148135/i-am-trying-to-match-the-last-non-blank-cell-in-a-row-with-the-header-above-it-i-used-this-formula-in-excel-lookup21l2r2m1s1-but-its-not-working-in/Can someone please help with this problem?Markc2265Sun, 04 Mar 2018 01:07:59 +0100https://ask.libreoffice.org/en/question/148135/Libreoffice 6 calc: speed up vlookup, match, index searchhttps://ask.libreoffice.org/en/question/147467/libreoffice-6-calc-speed-up-vlookup-match-index-search/Hi,
I have installed libreoffice 6.0.1.1 under ubuntu mate 16.04 64 bit. I have a calc file with 35 sheets. In particular, one sheet is made of about 500 rows and about 50 columns.
The sheet has 2 columns with match and index and 1 column with vlookup while the other are simple formulas as countif, count and four operations.
When I add a new row or when I change one value in a cell that requires to update the entire row, the update is extremely slow about 60 seconds. The same when I save the file.
My formulas are:
Nested match, index
=MATCH(1,INDEX(($S$18:$S$463=LARGE($S$18:$S$463,ROWS(B$17:B445)))*(COUNTIF(B$17:B445,$A$18:$A$463)=0),),0)
Nested index, match, index
=INDEX($A$18:$A$463,MATCH(1,INDEX(($AS$18:$AS$463=LARGE($AS$18:$AS$463,ROWS(AW$17:AW445)))*(COUNTIF(AW$17:AW445,$A$18:$A$463)=0),),0))
Standard vlookup
=VLOOKUP(AW446,$A$18:$AS$463,45,0)
I disabled openGL, experimental features and search criteria via:
> Menu/Tools/Options/LibreOffice
> calc/Calculate - Searc criteria = and
> <> must apply to whole cells.
Unfortunately, I cannot speed the process. I tried also to copy this sheet into a new empty file, but the result is more or less the same. I cannot use openCL since it is not supported by GPU and enabling or disabling it does not make any effect.
What can I do in order to make usable the sheet?
Thank youerotavlasSun, 25 Feb 2018 18:34:06 +0100https://ask.libreoffice.org/en/question/147467/=MATCH confuses "AV." with "AVV"https://ask.libreoffice.org/en/question/147211/match-confuses-av-with-avv/I'm using the following formula to look up a stock code and return it's line number:
>
=MATCH(T(B12),HL_download.$B$1:$B$202,0)
>
Within the "HL_download" sheet there are a number of stock codes, and two are similar:
>
AVV
>
AV.
>
They are listed in the order above, i.e. AVV first.
The MATCH command fails to pick up "AV.", always picking up "AVV". The only way around this is to remove the "." from "AV."
Can anyone explain why?
Is there something I can to to avoid this problem?
Thanks.jamesbThu, 22 Feb 2018 22:35:53 +0100https://ask.libreoffice.org/en/question/147211/reference sheet name from cell value in match formulahttps://ask.libreoffice.org/en/question/144983/reference-sheet-name-from-cell-value-in-match-formula/I have one sheet with:
- column A filled with dates in ascending order
- row 1 filled with currency tickers (USD, JPY, EUR, AUD, CHF)
several other sheets each named exactly as the currency tickers
each sheet as column A filled with dates in ascending order, column B with the currency daily average
I am trying to fill cells in my first sheet with values from the other sheet based on the row's date in column A and the column's ticker name in row 1 as the sheet name to look at.
assuming I am in ROW 547 and COLUMN G , I am using this formula in my first sheet:
=INDIRECT(G1&"."&ADDRESS(MATCH(A547,G1&"."&$A$1:$A$1000,-1),2))
but I am stuck with the MATCH formula:
this works:
=MATCH(A547,USD.$A$1:$A$1000,-1)
this doesn't:
=MATCH(A547,G1&"."&$A$1:$A$1000,-1)
as it appears like the range $A$1:$A$1000 is in the same sheet where the formula is and not in the one named in the cell G! as I am hoping.
any help is appreciated...
MikeleSat, 03 Feb 2018 02:01:44 +0100https://ask.libreoffice.org/en/question/144983/Get field by matching ID from another sheethttps://ask.libreoffice.org/en/question/142402/get-field-by-matching-id-from-another-sheet/I would like to get field contents (latitude & longitude) from sheet 2 into sheet 1 by matching their IDs. My dataset is the Form EIA-860 Data about power plants in the US.
Probably I should do this with a MATCH function, but couldn't figure out how.
![Screenshot](https://user-images.githubusercontent.com/35149775/34640872-54d07e02-f2fb-11e7-81b7-7259b097e6c1.png)
I've uploaded my document here: [XUP.IN](https://www.xup.in/dl,51045300/match.ods/)rosiraabSat, 06 Jan 2018 16:13:14 +0100https://ask.libreoffice.org/en/question/142402/Paste and match style?https://ask.libreoffice.org/en/question/96495/paste-and-match-style/Hello all, just wondering if LO has a "paste and match style" function, such as the one available on mac osx (**edit > paste and match style** or **option shift command v**)
Thanks.
EDIT: I am closing this question now, so I will clarify this to anyone wanting a concise answer to this question:
TO PASTE AND MATCH STYLE IN **WRITER:**
MAC: **Command + Option + Shift + v** then select "**unformatted text**".
WINDOWS: **Ctrl + Alt + Shift v** then select "**unformatted text**".
TO PASTE AND MATCH STYLE IN **CALC:**
MAC/WINDOWS: **Left-click the cell you want to paste into > Right-click > Paste Special > Unformatted Text**appreciatethehelpSat, 20 May 2017 20:07:11 +0200https://ask.libreoffice.org/en/question/96495/Returning contents of row below if specific text is found in a columns' listhttps://ask.libreoffice.org/en/question/97266/returning-contents-of-row-below-if-specific-text-is-found-in-a-columns-list/ Hello,
I am new here, and I have searched the forums to check whether this question has been dealt with but I can't seem to find the exact answer.
I have a long list of customer IDs (ALLINFO) and other details (of which I want the email addresses). Unfortunately, the associated email addresses are mixed in with this long list of IDs.
All data is in column A of a calc sheet I have. I have managed to pull all the IDs to a column of a new sheet (customerinfo, col A), and I would like to now paste the email address associated to each of these IDs in to a new column (col B).
The list of info is such that when there is an email address, I know for sure that it belongs to the customer ID just above it. I assume I have to do a MATCH INDEX mix, but I don't understand how I can ask it to pull out the cell content containing the email address which is just below it.
So in my new sheet I would like to have something like this:
Customer ID_A - If [Customer ID_A is in list on sheet ALLINFO, give me the row just below it, else nothing]
Customer ID_B - If [Customer ID_B is in list on sheet ALLINFO, give me the row just below it, else nothing]
and so on.
Do I have to do a SEARCH, MATCH, INDEX, LOOKUP ?? Am very confused as to what's the best way forward.
Thanks for any help you can give.dealerzMon, 29 May 2017 14:21:35 +0200https://ask.libreoffice.org/en/question/97266/functionaccess "offset" & "match"https://ask.libreoffice.org/en/question/92247/functionaccess-offset-match/Hi!
I would like to use the function "OFFSET" and "MATCH" in libreoffice macro,
I had found this topic, but it is not clear:
https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=40027
Can You help me?
Thank you in advanced
Have a Nice Day!
Fede
LO: 5.2.6 e 5.3.1
SO: Win 7 - 10FedeFri, 07 Apr 2017 08:42:54 +0200https://ask.libreoffice.org/en/question/92247/The Function Match in libreCalc behaves not like Excelhttps://ask.libreoffice.org/en/question/89815/the-function-match-in-librecalc-behaves-not-like-excel/![image description](http://www.bumbumtschak.com/img/share/excelvslibre.PNG)
The Funktion is: `=MATCH(1;B1:B15)`
Excel Outputs 8, libreCalc 15. I don't know which one is the right behaviour.
Export as xls or opening the ods in Excel does not change anything.
LibreOffice V5.3.0.3
Excel: 16.0.4498.1000
polleThu, 09 Mar 2017 13:51:13 +0100https://ask.libreoffice.org/en/question/89815/Find max cells in a rangehttps://ask.libreoffice.org/en/question/86821/find-max-cells-in-a-range/ I have a spreadsheet of 'scores' by user and I want to know which user has the max score. Users can 'tie' - ie have the same score, so I want to select the 'tied' users.
I am using:
=IF(MAX(B8:M8)=0,"No Winner",CONCATENATE(INDEX($B$4:$M$4,MATCH(LARGE(B8:M8,1),B8:M8,0)),",",INDEX($B$4:$M$4,MATCH(LARGE(B8:M8,2),B8:M8,0))))
BUT that has problems as it just repeats the same user. I think I want to be able to adjust the range in the second check - ie if the 'winner' is I8 to then start the search for the tied account over the range I8+1:M8 - but I can't see how to do that. Can someone give me a pointer?
Thanks,
Steve
steveTuFri, 03 Feb 2017 11:18:40 +0100https://ask.libreoffice.org/en/question/86821/Cells no longer match text after copy pastehttps://ask.libreoffice.org/en/question/81572/cells-no-longer-match-text-after-copy-paste/Hi,
TL;DR; Copying cells down one cell and updating references no longer matches text as it did in first row of cells.
I have a set of cells that I wanted to cut/paste (or copy/paste/delete first row) so that I could still use a sumif statement to read across the top and and information relevant from those columns as it had before.
Starting in P11 and going to AL11 I have a number of columns (Using Bold), thus
> Placement |Site | Rating | Placement |Site | Rating | Placement |Site | Rating
In F11 I have (In a spanning cell, Using bold):
> Placement
In F13 I have a formula:
> =SUMIF($P$11:$AL$11,$F$11,$P13:$AL13)
This All Works - All columns information in the row that have a header of "Placement" are added together.
I then Cut/Paste P11:AL11 to P12:AL12. Then I re-visit F13 and change the formula to:
> =SUMIF($P$12:$AL$12,$F$11,$P13:$AL13)
Then fill-down (via drag or CTL-D)The first row sums to 1 (first number it sees under a placement). The second row, previously, summed to 16 - but is now #N/A (as are all following rows).
Here is a copy/paste of row 18 (that now reads #N/A):
> =SUMIF($P$12:$AL$12,$F$11,$P18:$AL18)
Now if I copy/paste P12:AL12 back into P11:AL11 all the numbers sum as they should under each heading of Placement.
If I delete P12:AL12, now F13 breaks and F14 sums only the first number it sees under a "Placement". All other cells turn to 0.
I thought maybe its something to do with range/column naming and have checked under Insert > Named Expressions > [Everything]. But there are no names or defined column headings.
Version was 5.2.2.2
Update to try to fix to 5.2.3.3 (x64)
I have tried multiple "Paste Special" options, including all. As well as copying/pasting each cell down individually.
Any thoughts on this? Thanks!AllisterFri, 11 Nov 2016 02:25:13 +0100https://ask.libreoffice.org/en/question/81572/