Be Excellent To Each Other
https://www.beexcellenttoeachother.com/forum/

Excel Help
https://www.beexcellenttoeachother.com/forum/viewtopic.php?f=3&t=9857		 Page 1 of 2
Author:  Mr Russell [ Wed Dec 11, 2013 17:00 ]
Post subject:  Excel Help
Hi all,
I've got a list of news product barcodes, a list of issues, and a list of retail prices e.g.
Barcode | Issue# | Price
123 | 1 | 70
123 | 2 | 70
123 | 3 | 75
245 | 1 | 45
245 | 2 | 45
245 | 3 | 55
245 | 4 | 55
etc

So the barcode and issue number together make a unique reference, and the retail price may go up as new issues come out.

I would like to add a new column that is basically MAX retail price, and that should show against each row, so the grid would now look like:
123 | 1 | 70 | 75
123 | 2 | 70 | 75
123 | 3 | 75 | 75
245 | 1 | 45 | 55
245 | 2 | 45 | 55
245 | 3 | 45 | 55
245 | 4 | 45 | 55

because it has selected the max retail price for each unique barcode, and put it against each barcode.

Does that make sense?

Is it possible?
Author:  Trooper [ Wed Dec 11, 2013 17:07 ]
Post subject:  Re: Excel Help
Yeah totally possible.
Author:  GazChap [ Wed Dec 11, 2013 17:08 ]
Post subject:  Re: Excel Help
=MAX(IF($A$1:$A$1000=A1,$C$1:$C$1000,FALSE))

When you enter that into the first row, don't press ENTER to save it to the cell, press CTRL+Shift+Enter - that will make it an array formula. Then you can just fill it down by double-clicking the fill handle, job jobbed.

That's assuming there's no column headers - if there are, then all of the 1's should be 2's, and the 1000s should be the highest row number you have.
Author:  Mr Russell [ Wed Dec 11, 2013 18:31 ]
Post subject:  Re: Excel Help
GazChap wrote:
=MAX(IF($A$1:$A$1000=A1,$C$1:$C$1000,FALSE))

When you enter that into the first row, don't press ENTER to save it to the cell, press CTRL+Shift+Enter - that will make it an array formula. Then you can just fill it down by double-clicking the fill handle, job jobbed.

That's assuming there's no column headers - if there are, then all of the 1's should be 2's, and the 1000s should be the highest row number you have.

Thanks, I've gone home now but I'll try it tomorrow :)
Author:  Mr Russell [ Thu Dec 12, 2013 9:56 ]
Post subject:  Re: Excel Help
Gaz that was spot on, thank you.

My formula in the end was =MAX(IF($B$2:$B$40651=B2,$O$2:$O$40651,FALSE)) (due to other columns) so I guess you can see why I didn't want to do this manually.

Massive thanks.
Author:  Mr Russell [ Thu Dec 12, 2013 9:56 ]
Post subject:  Re: Excel Help
Trooper, you're a dick.
Author:  DavPaz [ Thu Dec 12, 2013 10:13 ]
Post subject:  Re: Excel Help
Mr Russell Sprouts wrote:
Trooper, you're a dick.

But... He was right!
Author:  Mr Russell [ Thu Dec 12, 2013 10:16 ]
Post subject:  Re: Excel Help
DavPaz wrote:
Mr Russell Sprouts wrote:
Trooper, you're a dick.

But... He was right!


But he ignored my first question of 'Does that make sense' and I was tossing and turning all night with the worry that it hadn't made sense.
Author:  Trooper [ Thu Dec 12, 2013 10:18 ]
Post subject:  Re: Excel Help
Mr Russell Sprouts wrote:
DavPaz wrote:
Mr Russell Sprouts wrote:
Trooper, you're a dick.

But... He was right!


But he ignored my first question of 'Does that make sense' and I was tossing and turning all night with the worry that it hadn't made sense.


No I didn't, insert imaginary comma.
Author:  Mr Russell [ Thu Dec 12, 2013 10:28 ]
Post subject:  Re: Excel Help
I take it all back. Trooper, you were gushingly amazing!
Author:  Trooper [ Thu Dec 12, 2013 12:03 ]
Post subject:  Re: Excel Help
Obviously, that's my default state.
Author:  allensmith [ Thu Jan 22, 2015 11:10 ]
Post subject:  Re: Excel Help
I am afraid that it's impossible to do that.
Author:  Cras [ Thu Jan 22, 2015 11:16 ]
Post subject:  Re: Excel Help
Yeah, alright, chill out HAL.
Author:  Mr Russell [ Thu Jan 22, 2015 12:38 ]
Post subject:  Re: Excel Help
allensmith obviously missed GazChaps's post :)
Author:  Mimi [ Thu Jan 22, 2015 18:39 ]
Post subject:  Re: Excel Help
more importantly he missed Trooper's, which was pretty explicit.
Author:  Cras [ Thu Jan 22, 2015 18:48 ]
Post subject:  Re: Excel Help
He was a blatant spambot ;)
Author:  Mimi [ Thu Jan 22, 2015 18:50 ]
Post subject:  Re: Excel Help
Yeah, Trooper just posts spammy crap ALL THE TIME.

Allan Smith seems great and full of informative help, though. Get him back here. Him and Joans could be a double act.
Author:  TheVision [ Fri Jan 23, 2015 15:23 ]
Post subject:  Re: Excel Help
I need Excel help. I have my gaming spreadsheet with the date I bought each game in it's own column. I have a separate column with just the year in. What I'd like to do is have a formula which works out the average price per game in a given year.

I have peripherals on a separate sheet which also need to be factored in.

I think it's quite simple but excel confuses me... Clearly.
Author:  MaliA [ Fri Jan 23, 2015 15:24 ]
Post subject:  Re: Excel Help
Pivot it. Insert a pivot table, Year in rows and average in values
Author:  Cras [ Fri Jan 23, 2015 15:25 ]
Post subject:  Re: Excel Help
Just a little bit
Author:  Joans [ Fri Jan 23, 2015 23:32 ]
Post subject:  Re: Excel Help
Sumif and countif?
Author:  Cras [ Tue May 19, 2015 16:24 ]
Post subject:  Re: Excel Help
Hrmn. I have two columns. Neither contains unique values. I want to know any values in the first column where the values in the second column differ. An example:
Code:
Jeff            1
Jeff            1
Jeff            1
Dave          1
Dave          2
Susan         3
Susan         3


So in that example Jeff and Susan are fine, I don't care about them - but Dave has two different values against him in column 2, so I want to know about him. Thoughts on how to do this?

Cheers
Author:  MaliA [ Tue May 19, 2015 16:29 ]
Post subject:  Re: Excel Help
Pivot it and take an average?
Author:  Cras [ Tue May 19, 2015 16:33 ]
Post subject:  Re: Excel Help
The second column isn't actually numeric in the real data, so an average wouldn't work.
Author:  MaliA [ Tue May 19, 2015 16:36 ]
Post subject:  Re: Excel Help
Use an if statement to see if the cell matches the one above?
Author:  Grim... [ Tue May 19, 2015 16:39 ]
Post subject:  Re: Excel Help
Col C:
Code:
=IF(VLOOKUP(A1,A:B,2,FALSE)<>B1,"!","")
Author:  Cras [ Tue May 19, 2015 16:55 ]
Post subject:  Re: Excel Help
Grim... wrote:
Col C:
Code:
=IF(VLOOKUP(A1,A:B,2,FALSE)<>B1,"!","")


That didn't work, unfortunately.

However, I solved it in tremendously clunky fashion! I did a pivot, then used countblank to count which cells in each row were blank. If the count wasn't one less than the total possible different values of column 2, then that person had two different values. Messy but worked.
Author:  Grim... [ Tue May 19, 2015 17:13 ]
Post subject:  Re: Excel Help
Cras wrote:
Grim... wrote:
Col C:
Code:
=IF(VLOOKUP(A1,A:B,2,FALSE)<>B1,"!","")


That didn't work, unfortunately.

It did too. There must have been something about your data you didn't mention.
Author:  Bobbyaro [ Wed May 20, 2015 8:24 ]
Post subject:  Re: Excel Help
Just out of curiosity, would this have been sufficient?
=IF(AND(A2=A1,B2<>B1),1,0)
Author:  Grim... [ Wed May 20, 2015 9:14 ]
Post subject:  Re: Excel Help
Looks like it would be - you'd have to order A first, of course, but that's no hardship.
Author:  Cras [ Wed May 20, 2015 9:43 ]
Post subject:  Re: Excel Help
Yup - that works a treat.
Author:  Cras [ Wed May 20, 2015 9:47 ]
Post subject:  Re: Excel Help
Oh, huh - and the vlookup works today, too. Didn't yesterday.
Author:  Bobbyaro [ Wed May 20, 2015 10:11 ]
Post subject:  Re: Excel Help
if you sorted the list to make mine work, it may have made the vlookup work, vlookup sometimes doesn't work on unsorted lists (stupidly)
Author:  Grim... [ Wed May 20, 2015 10:38 ]
Post subject:  Re: Excel Help
Bobbyaro wrote:
if you sorted the list to make mine work, it may have made the vlookup work, vlookup sometimes doesn't work on unsorted lists (stupidly)

Wait, what?!

I know sorting them makes it (far) faster, but I can't see how it would stop it working at all. That's the whole point of it, surely?
Author:  Bobbyaro [ Wed May 20, 2015 11:28 ]
Post subject:  Re: Excel Help
https://support.microsoft.com/en-us/kb/181212

Quote:
LOOKUP requires that the first column of the vector (or the first column or row for the array form) is sorted in ascending order. The following information describes different formulas that you can use to return the same information returned by LOOKUP without requiring that the first column of the table be sorted.
Author:  Grim... [ Wed May 20, 2015 11:36 ]
Post subject:  Re: Excel Help
Well damn.

Hopefully that's just for 2003, though?
Author:  GazChap [ Wed May 20, 2015 16:44 ]
Post subject:  Re: Excel Help
Pretty sure I constantly run in to the same issue on 2010 and 2013.

I fucking hate VLOOKUP as a result.
Author:  MaliA [ Fri May 29, 2015 14:39 ]
Post subject:  Re: Excel Help
in cell I6 I can select the last cell in the row using "=lookup(1E+100,B6:DW6)" to pull back last week's sales data. In the next cell,J6,I want to then return the value 52 columns before that (same week, last year). So going to the last populated cell, then counting back. I am struggling with this one.
Author:  Grim... [ Fri May 29, 2015 15:02 ]
Post subject:  Re: Excel Help
Code:
=INDEX(A6:ZZ6,COUNT(A6:ZZ6))
will get you the last value in row 6, so you want
Code:
=INDEX(A6:ZZ6,COUNT(A6:ZZ6)-52)


Edge case: Some years have 53 weeks - make sure that doesn't muck up your result.
Author:  MaliA [ Fri May 29, 2015 15:05 ]
Post subject:  Re: Excel Help
Grim... wrote:
Code:
=INDEX(A6:ZZ6,COUNT(A6:ZZ6))
will get you the last value in row 6, so you want
Code:
=INDEX(A6:ZZ6,COUNT(A6:ZZ6)-52)


Edge case: Some years have 53 weeks - make sure that doesn't muck up your result.


Thank you.
Author:  MaliA [ Fri May 29, 2015 15:09 ]
Post subject:  Re: Excel Help
No. That isn't returning the last populated cell.
Author:  Grim... [ Fri May 29, 2015 15:11 ]
Post subject:  Re: Excel Help
It does in Excel 2013. Is their other odd stuff going on that you're not telling us about?
Author:  Grim... [ Fri May 29, 2015 15:13 ]
Post subject:  Re: Excel Help
Wait - are all the cells in that row populated?
Author:  MaliA [ Fri May 29, 2015 15:15 ]
Post subject:  Re: Excel Help
Grim... wrote:
It does in Excel 2013. Is their other odd stuff going on that you're not telling us about?


No,I was being daft. it works, thank you. It was pulling a zero value due to a vlookup being on the end. I have adjusted it now. Thank you. Much obliged.
Author:  Grim... [ Fri May 29, 2015 15:17 ]
Post subject:  Re: Excel Help
Or - d'uh - if it's not populated with number then swap COUNT for COUNTA. I'm not sure why I thought they would all be numbers, to be honest.

[edit] Hurrah!
Author:  MaliA [ Thu Aug 06, 2015 10:16 ]
Post subject:  Re: Excel Help
How can i get excel to let me have one sheet per screen? It tiles sheets in one window.
Author:  Grim... [ Thu Aug 06, 2015 10:18 ]
Post subject:  Re: Excel Help
What Office version are you on? If you're on 2013 you want View | New Window.

Also, I take it you mean two sheets from the same file (if not, just open a new Excel instance by shift-left clicking on the Excel icon)?
Author:  TheVision [ Thu Aug 06, 2015 10:23 ]
Post subject:  Re: Excel Help
Grim... wrote:
(if not, just open a new Excel instance by shift-left clicking on the Excel icon)?


Woah! I didn't know this! Great work.
Author:  MaliA [ Thu Aug 06, 2015 10:24 ]
Post subject:  Re: Excel Help
Grim... wrote:
What Office version are you on? If you're on 2013 you want View | New Window.

Also, I take it you mean two sheets from the same file (if not, just open a new Excel instance by shift-left clicking on the Excel icon)?


I want file A on monitor A and file b on monitor b. Excel 2007
Author:  Grim... [ Thu Aug 06, 2015 10:26 ]
Post subject:  Re: Excel Help
Just open a new instance of Excel, either by holding shift down while you open it, or by clicking on it with the middle mouse button (sometimes).
Page 1 of 2 All times are UTC [ DST ]
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/