Excel Question

Mars

Mars

Honorary Master
Joined
Feb 4, 2006
Messages
11,452
Reaction score
1,149
Location
Cape Town
IE: I need to draw any information that have the same invoice number into one row on a different sheet if the row has a certain code.

From:
Code: 
|#| Invoice   |  Item   |  Qty    |
|2| 1234      | 6763    |  3      |
|2| 1234      | 6764    |  4      | 
|2| 1234      | 6766    |  7      | 
|2| 1234      | 6769    |  5      | 
|2| 1235      | 6763    |  3      |
|2| 1235      | 6764    |  4      | 
|2| 1235      | 6766    |  7      | 
|2| 1235      | 6769    |  5      |

So sheet "2" will show the following info:
Code: 
| Invoice   |  Item   |  Qty   |   Item   |  Qty    |    Item   |  Qty   |    Item   |  Qty   |
| 1234      | 6763    |  3     |  6764    |  4      |   6766    |  7     |   6769    |  5     | 
| 1235      | 6763    |  3     |  6764    |  4      |   6766    |  7     |   6769    |  5     |

I would like it to be automated somehow but I have no idea where to start.
 
You need to play with Pivot Tables then...

From your basic example, a Pivot Table with Item as a Column Label, Invoice as a Row Label and Sum of QTY as the Value
 
ToxicBunny said:
You need to play with Pivot Tables then...

From your basic example, a Pivot Table with Item as a Column Label, Invoice as a Row Label and Sum of QTY as the Value
Click to expand...

This would be the most elegant solution. You could do it using other methods but they will be overly complicated and not as robust as a pivot table.
 
ToxicBunny said:
You need to play with Pivot Tables then...

From your basic example, a Pivot Table with Item as a Column Label, Invoice as a Row Label and Sum of QTY as the Value
Click to expand...

DrJohnZoidberg said:
This would be the most elegant solution. You could do it using other methods but they will be overly complicated and not as robust as a pivot table.
Click to expand...

Thanks, this seems like the way to go. I've always avoided pivot tables, guess its time to start learning :/
 
My biggest stumbling block with the pivot tables right now is that I have to reference the results.
Since the table layout is dynamic, I'm not sure how I'm going to do this.
 
Madman88 said:
My biggest stumbling block with the pivot tables right now is that I have to reference the results.
Since the table layout is dynamic, I'm not sure how I'm going to do this.
Click to expand...

How much data are you working with and where are trying to reference these results? It may be easier if you use a database to store this data.
 
DrJohnZoidberg said:
How much data are you working with and where are trying to reference these results? It may be easier if you use a database to store this data.
Click to expand...

Yea, maybe. The problem is I know quite a bit about excel and jack shlt about databases :p
 
I have looked at access before. I ended up going back to excel. just too confusing. I need to do some (very) basic database course or something.
 
Madman88 said:
I have looked at access before. I ended up going back to excel. just too confusing. I need to do some (very) basic database course or something.
Click to expand...

I use Excel / Access all the time... linked tables between the two etc... access queries can do a lot when compared to straight forward excel sheets....
 
ok. Where do I get a version of access 2010?

I dont want to use 2013 since there is no runtime for it and I dont want to rely on being online to use it.
I do like the ability to create browser based bd's versus using an SDK, but broadband is just to unreliable for me to commit to a cloud based program.
 
SirFooK'nG said:
I use Excel / Access all the time... linked tables between the two etc... access queries can do a lot when compared to straight forward excel sheets....
Click to expand...

Do you keep the data in spreadsheets and query from access?
 
Madman88 said:
ok. Where do I get a version of access 2010?

I dont want to use 2013 since there is no runtime for it and I dont want to rely on being online to use it.
I do like the ability to create browser based bd's versus using an SDK, but broadband is just to unreliable for me to commit to a cloud based program.
Click to expand...

Lol. You still struggling? :D

You would have to purchase a copy of Office 2010. You could always "purchase" a copy online ;)
 
Madman88 said:
Yea, maybe. The problem is I know quite a bit about excel and jack shlt about databases :p
Click to expand...

And pivot tables. You sound like me :p I've always avoided these as well.

You've inspired me to learn about PTs now. I'm on HowToGeek :D
 
DrJohnZoidberg said:
Life. Changing. * If you use Excel a lot.
Click to expand...

Don't get me wrong, I can help myself quite nicely in excel for my needs. Especially on formulas and conditional formats which I like. Database functions however are very limited and getting PTs under the belt will solve that problem for one.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X