Be Excellent To Each Other

And, you know, party on. Dude.

All times are UTC [ DST ]




Reply to topic  [ 75 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Excel Help
PostPosted: Wed Dec 11, 2013 17:00 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 12240
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?

_________________
Always proof read carefully in case you any words out


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed Dec 11, 2013 17:07 
User avatar
ugvm'er at heart...

Joined: 4th Mar, 2010
Posts: 22256
Yeah totally possible.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed Dec 11, 2013 17:08 
User avatar

Joined: 30th Mar, 2008
Posts: 14130
Location: Shropshire, UK
=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.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed Dec 11, 2013 18:31 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 12240
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 :)

_________________
Always proof read carefully in case you any words out


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Dec 12, 2013 9:56 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 12240
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.

_________________
Always proof read carefully in case you any words out


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Dec 12, 2013 9:56 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 12240
Trooper, you're a dick.

_________________
Always proof read carefully in case you any words out


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Dec 12, 2013 10:13 
User avatar
Unpossible!

Joined: 27th Jun, 2008
Posts: 38439
Mr Russell Sprouts wrote:
Trooper, you're a dick.

But... He was right!


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Dec 12, 2013 10:16 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 12240
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.

_________________
Always proof read carefully in case you any words out


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Dec 12, 2013 10:18 
User avatar
ugvm'er at heart...

Joined: 4th Mar, 2010
Posts: 22256
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.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Dec 12, 2013 10:28 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 12240
I take it all back. Trooper, you were gushingly amazing!

_________________
Always proof read carefully in case you any words out


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Dec 12, 2013 12:03 
User avatar
ugvm'er at heart...

Joined: 4th Mar, 2010
Posts: 22256
Obviously, that's my default state.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Jan 22, 2015 11:10 
Excellent Member

Joined: 21st Jan, 2015
Posts: 1
I am afraid that it's impossible to do that.

_________________
ean13 excel barcode


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Jan 22, 2015 11:16 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Yeah, alright, chill out HAL.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Jan 22, 2015 12:38 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 12240
allensmith obviously missed GazChaps's post :)

_________________
Always proof read carefully in case you any words out


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Jan 22, 2015 18:39 
User avatar

Joined: 27th Mar, 2008
Posts: 25549
more importantly he missed Trooper's, which was pretty explicit.

_________________
Image


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Jan 22, 2015 18:48 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
He was a blatant spambot ;)

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Jan 22, 2015 18:50 
User avatar

Joined: 27th Mar, 2008
Posts: 25549
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.

_________________
Image


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri Jan 23, 2015 15:23 
SupaMod
User avatar
"Praisebot"

Joined: 30th Mar, 2008
Posts: 17013
Location: Parts unknown
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.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri Jan 23, 2015 15:24 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
Pivot it. Insert a pivot table, Year in rows and average in values

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri Jan 23, 2015 15:25 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Just a little bit

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri Jan 23, 2015 23:32 
User avatar

Joined: 31st Mar, 2008
Posts: 8647
Sumif and countif?


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Tue May 19, 2015 16:24 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
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

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Tue May 19, 2015 16:29 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
Pivot it and take an average?

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Tue May 19, 2015 16:33 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
The second column isn't actually numeric in the real data, so an average wouldn't work.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Tue May 19, 2015 16:36 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
Use an if statement to see if the cell matches the one above?

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Tue May 19, 2015 16:39 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
Col C:
Code:
=IF(VLOOKUP(A1,A:B,2,FALSE)<>B1,"!","")

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Tue May 19, 2015 16:55 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
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.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Tue May 19, 2015 17:13 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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.


You do not have the required permissions to view the files attached to this post.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 8:24 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
Just out of curiosity, would this have been sufficient?
=IF(AND(A2=A1,B2<>B1),1,0)


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 9:14 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
Looks like it would be - you'd have to order A first, of course, but that's no hardship.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 9:43 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Yup - that works a treat.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 9:47 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49232
Oh, huh - and the vlookup works today, too. Didn't yesterday.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 10:11 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
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)


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 10:38 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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?

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 11:28 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11843
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.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 11:36 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
Well damn.

Hopefully that's just for 2003, though?

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Wed May 20, 2015 16:44 
User avatar

Joined: 30th Mar, 2008
Posts: 14130
Location: Shropshire, UK
Pretty sure I constantly run in to the same issue on 2010 and 2013.

I fucking hate VLOOKUP as a result.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 14:39 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
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.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:02 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:05 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
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.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:09 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
No. That isn't returning the last populated cell.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:11 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
It does in Excel 2013. Is their other odd stuff going on that you're not telling us about?

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:13 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
Wait - are all the cells in that row populated?

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:15 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
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.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Fri May 29, 2015 15:17 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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!

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:16 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
How can i get excel to let me have one sheet per screen? It tiles sheets in one window.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:18 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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)?

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:23 
SupaMod
User avatar
"Praisebot"

Joined: 30th Mar, 2008
Posts: 17013
Location: Parts unknown
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.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:24 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48607
Location: Cheshire
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

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel Help
PostPosted: Thu Aug 06, 2015 10:26 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 69502
Location: Your Mum
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).

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
Display posts from previous:  Sort by  
Reply to topic  [ 75 posts ]  Go to page 1, 2  Next

All times are UTC [ DST ]


Who is online

Users browsing this forum: Columbo, Kern and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search within this thread:
You are using the 'Ted' forum. Bill doesn't really exist any more. Bogus!
Want to help out with the hosting / advertising costs? That's very nice of you.
Are you on a mobile phone? Try http://beex.co.uk/m/
RIP, Owen. RIP, MrC.

Powered by a very Grim... version of phpBB © 2000, 2002, 2005, 2007 phpBB Group.