for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

Dave..M May 2023 10:55pm, 30 May 2023 224 posts	Quote Pin Dave..M I tried xlookup for the first time today and was quite impressed. Only a sample size of 1 but seemed easier than lookup
Diogenes Jun 2023 11:51am, 21 Jun 2023 79,747 posts	Quote Pin Diogenes This should be easy but is defeating me at the moment. I want to highlight a cell where the value in H2 matches the one if A2 (and the same for every cell in column H.) I've been attempting this using conditional formatting so far, but not got it working.
um Jun 2023 2:31pm, 21 Jun 2023 7,477 posts	Quote Pin um Dio - my way would be - select all cols A:H - select conditional formatiing, - add new rule - "=$A1=$H1" - add the format you want (eg, on a small sample. with A1 to H5 selected
Diogenes Jun 2023 3:54pm, 21 Jun 2023 79,759 posts	Quote Pin Diogenes Thanks Um, I'll try that out. In the end it was quicker to do it without the visual aid.
um Jun 2023 3:57pm, 21 Jun 2023 7,478 posts	Quote Pin um You could always insert an extra column and populate with (eg) =if(An=Hn,"*","")
larkim Jul 2023 4:26pm, 4 Jul 2023 21,441 posts	Quote Pin larkim I've got a series of pieces of data that come out of a report in an unhelpful way. Essentially, the top part is a list of salary scales, and the bottom part is a list of job grades and their max / min salary grade. I need a vertical list which I can use as a combo of "Job_Grade&Salary_Grade" for other purposes. The issue is that the overall report is long and has multiple sections to it. I can segment between the sections with some helper columns picking out unique characteristics of the first row of any section, but not managed to easily drop in formulas that will create my vertical list (ideally next to the top part). An example may help - here is one of the sections in a google spreadsheet docs.google.com Any ideas how to achieve? I've manually typed my "goal" output for that section which may explain things more clearly than I am doing here!
um Jul 2023 5:22pm, 4 Jul 2023 7,514 posts	Quote Pin um Are the points and codes unique AND all the points numeric and all the codes alphamumeric? If so, I'd filter and split the original into 2 sections/sheets to produce one with just points & values, the second with grade codes and point range values.
larkim Jul 2023 6:32pm, 4 Jul 2023 21,442 posts	Quote Pin larkim Thanks, I want to keep the report in one piece so I can reapply the formulae every now and then without having to rework the spreadsheet. But just before leaving work I stumbled upon a use of xlookup to sort it out I think. When I'm back on a laptop I'll post the solution if nothing occurs to me overnight to say it's wrong!
run free Jul 2023 5:25pm, 5 Jul 2023 5,309 posts	Quote Pin run free Might also want to look at the FILTER() and UNIQUE()
um Jul 2023 5:40pm, 5 Jul 2023 7,519 posts	Quote Pin um larkim - any spreadsheets I used to regularly manipulate or maintain using multiple steps, as I recorded the steps once as a macro, tweaked the macro to make it generic (eg rows to 99999 or more) and then re-ran the macro to do the work. Keeping it simple makes it a lot easier (at least it did for me) to maintain over the years if an when sources changed.