Excel Help

crodonilson

He/Him

Jan 17, 2005

13,513

Lyme Regis

• Nov 13, 2013

• #1

I have to do an order with a supplier, I have a list of products I need to order and I have a list of stores I need to order the products for but I need a consolidated list of all products against each store. Is there a quick way to do this with formula rather than manually copying and pasting the order list x number of times for each store??

 

mejonaNO12 aka riskit

Well-known member

Dec 4, 2003

21,492

England

• Nov 13, 2013

• #2

crodonilson said:

I have to do an order with a supplier, I have a list of products I need to order and I have a list of stores I need to order the products for but I need a consolidated list of all products against each store. Is there a quick way to do this with formula rather than manually copying and pasting the order list x number of times for each store??

Click to expand...

You can obviously re-order it to seperate the list by Stores.

Just highlight the whole field of data and sort by A to Z.

Alternately you add an "if" formula to say something like "IF A3=jims shop, then show B3".
drag that formula all the way down and then it should just grab all the products associated to Jims store and put them as a seperate list.

I've probably not helped there. Sorry.

 

Albumen

Don't wait for me!

Jan 19, 2010

11,495

Brighton - In your face

• Nov 13, 2013

• #3

crodonilson said:

I have to do an order with a supplier, I have a list of products I need to order and I have a list of stores I need to order the products for but I need a consolidated list of all products against each store. Is there a quick way to do this with formula rather than manually copying and pasting the order list x number of times for each store??

Click to expand...

I think I need more info, by the sounds of it you're ordering the same list of products for each store. Is that right?

 

S

Shropshire Seagull

Well-known member

Nov 5, 2004

8,496

Telford

• Nov 13, 2013

• #4

As long as the store name is in one of the columns, use filter.
Then, by filtering on store name, you can see / print the order for each store.

 

R

reigate

New member

Nov 10, 2005

921

• Nov 13, 2013

• #5

crodonilson said:

I have to do an order with a supplier, I have a list of products I need to order and I have a list of stores I need to order the products for but I need a consolidated list of all products against each store. Is there a quick way to do this with formula rather than manually copying and pasting the order list x number of times for each store??

Click to expand...

Pivot Table?

 

crodonilson

He/Him

Jan 17, 2005

13,513

Lyme Regis

• Nov 13, 2013

• Thread starter
• #6

Albumen said:

I think I need more info, by the sounds of it you're ordering the same list of products for each store. Is that right?

Click to expand...

Yes, so in essence I have a colum with all of my stores in and a column with all of my products in. I need excel to then convert it into a long list with each store and all of the products against it.

 

Albion_Dave

Well-known member

Jul 4, 2011

2,108

Eastbourne

• Nov 13, 2013

• #7

As reigate said, pivot table sounds the way to go.

 

mejonaNO12 aka riskit

Well-known member

Dec 4, 2003

21,492

England

• Nov 13, 2013

• #8

crodonilson said:

Yes, so in essence I have a colum with all of my stores in and a column with all of my products in. I need excel to then convert it into a long list with each store and all of the products against it.

Click to expand...

All you need to do is add a filter. Firstly make sure there is a single cell gab at the top of your spreadsheet. This has to either be a gap or a header, for example "store" In the 'store' column click the colum letter (ie A). That will highlight the whole column. In the "DATA" tab select FILTER.

This will then create a drop down button next to the first cell in that column. Click on that and you can filter by any store you want. It will then show all the products linked with that store.

 

Albumen

Don't wait for me!

Jan 19, 2010

11,495

Brighton - In your face

• Nov 13, 2013

• #9

Stores:
Barnes
Bridcutt
Buckley

Orders:
Goal1
Goal2
Goal3

You want:

Barnes Goal1
Barnes Goal2
Barnes Goal3
Bridcutt Goal1
Bridcutt Goal2
Bridcutt Goal3
Buckley Goal1
Buckley Goal2
Buckley Goal3

?

 

Iggle Piggle

Well-known member

Sep 3, 2010

5,312

• Nov 13, 2013

• #10

Click the little box at the top of the page between Column 1 and row A which should have a little triangle symbol on it. This will highlight the data.

Right click and then choose ''Clear contents''

That should do it

 

Albumen

Don't wait for me!

Jan 19, 2010

11,495

Brighton - In your face

• Nov 13, 2013

• #11

Iggle Piggle said:

Click the little box at the top of the page between Column 1 and row A which should have a little triangle symbol on it. This will highlight the data.

Right click and then choose ''Clear contents''

That should do it

Click to expand...

hehe

 

crodonilson

He/Him

Jan 17, 2005

13,513

Lyme Regis

• Nov 13, 2013

• Thread starter
• #12

Albumen said:

Stores:
Barnes
Bridcutt
Buckley

Orders:
Goal1
Goal2
Goal3

You want:

Barnes Goal1
Barnes Goal2
Barnes Goal3
Bridcutt Goal1
Bridcutt Goal2
Bridcutt Goal3
Buckley Goal1
Buckley Goal2
Buckley Goal3

?

Click to expand...

Yes, that's exactly what I want.

 

Albumen

Don't wait for me!

Jan 19, 2010

11,495

Brighton - In your face

• Nov 13, 2013

• #13

crodonilson said:

Yes, that's exactly what I want.

Click to expand...

Maybe Access is the answer.

 

N

North East Seagull

Active member

Jul 6, 2004

121

Newcastle upon Tyne

• Nov 13, 2013

• #14

Sounds like a pivot table is the way to. Copy and paste special values to get the pivot as pure data. Use GoTo, Special, To fill in the blanks and then finally copy and paste the results as vaues to get a list os standalone values with no blanks. Takes about 45 seconds.

 

brakespear

Doctor Worm

Feb 24, 2009

12,326

Sleeping on the roof

• Nov 13, 2013

• #15

reigate said:

Pivot Table?

Click to expand...

yes, pivot table.