Inserting Category Beside Products
Jun 5, 2007I have an Excel Sheet which has the Products column. I need to insert Category beside every products.
View 7 Replies
ADVERTISEMENT
Excel 2010 :: Group Table By Category And Put Category In Its Own Row?
Sep 18, 2013Basically, I have a table of data in Excel 2010, akin to the demonstration below (just much, much, MUCH bigger).
Category
Genre
Title
Author
Year
[Code]....
Sum Of Products In Every Second Column
Jan 8, 2010I am sure this is simple but I have not found out the solution anyway....
Example:
A B C D E F G
1 2 100 0 0 2 100 400
2 2 100 10 50 0 0 700
3 2 100 0 0 2 200 600
Columns A, C and E are number of items on each "unit".
Columns B, D and F are the number of "units" (e.g. to produce).
Column G are the resulting number of items that need to be purchased.
So, column G is a sum of products and the formula found below if put in G1 will do the trick. =A1*B1+C1*D1+E1*F1. What I would like instead is to have a "formula" that allows me to insert new pairs of columns without having to change the formula to include the new column pairs.
Different Discounts For Different Products.
Jun 12, 2009I have a list of parts (3000+) that have different discounts.
When I enter the part#, I want the result to be"Price w/Discount.
I can do a vlookup, and also the index and match even with ctrl+shift+enter. But the results only give me just the price. If possible,
I would like for excel to do this without adding an extra column for the discount. I'm stumped on this.
Example:
In column A2:A13 I have part numbers, in column B2:B13 I have prices.
In column D2, I have the lookup and the result to be placed in column E2.
Note: I have a discount table in J2:J5 which range from
.61, .62, .72 and.79.
Database - Tried With All The Products In The Same File
Aug 11, 2009Basically its a invoicing system. I tried with all the products in the same excel file but then I realized if i update the product list it wont in all excel files for each customer. So i thought one external access database (or excel file) that I could refer to from an excel file.
So what I want to do now is when I want to add a new product to an invoice, I click a button (add) and it opens a small window with the list of products which has been taken from say an access database or excel file. Then I select the product and click insert.
How would I go about doing this? Are there any simple example I could follow?
Total Value Of Multiple Products
Mar 6, 2006i have a payroll grid. it has 3 cells with drop downs that have 39 options.
there codes for payments. i need a fourth cell to show a total dollor amount based of the codes selected.
separate question. i have a list of cells with the same four options (Job Types.ie service call, new connect...) i want to limit the cells mentioned before so that payment codes that do not pertain to the job type can not be selected.
Counting Products Sold Per Rep
Aug 27, 2008I'm trying to write a formula to count under certain conditions. I tried the following (and some other variations), but it doesn't work, it comes back #VALUE!:
=COUNTIFS('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100,D7,'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$A:$A,A34)
I need to count the number of products a rep sold in one day. He can have several sales in one day, and also sell more than one product per sale. The list I am pulling from has all sales from all reps for the day.
Compare Products On 2 Sheets
Mar 13, 2009I'm not 100% sure how to describe what I need to do but here goes. I have two similar spread sheets. The first contains all the products in a shop with the new prices and new item codes.
The second sheet has all the old item codes old prices and some duplicates and items that do not exist any more. Both spread sheets have lots of column but only 3 that I need to worry about. Item code, Product Name and Price.
What I have tried is using the If command (in the item code column) to search through the Product Names (on the updated sheet) and put the right item code next to the right product (on the old sheet) but it didn't work.
Create New Products Numbers
Jun 17, 2009I would like to create a new products numbers column in Excel.
I have my supplier "SKU's numbers" in column A , I would like to create my "SKU's numbers" in XXXX format (only numbers) in column B.
Could someone help me pls todo it with Macro or if it possible todo it in formula ?
The code should check in the excel sheet, that it is not generate a new SKU that was used before !
If Formula: Identify New Products That Are Not In My 'stocklist'
May 5, 2009I am trying to identify new products that are not in my 'stocklist'. In the 'new prices' sheet I have =IF(ISNUMBER(MATCH(C4,'Stock List'!C:C,0)),"","NEW") dragged down in column G. Though its throwing out 'New' on every item which is incorrect as there are loads of items already in both sheets. This sounds confusing but if you look at the attached im sure you will see what im trying to do.
One last problem is that some of the new codes have zeros in front of them so the formula will have to discount the 1st couple of digits IF they happen to be zero. example; C5480 in stock list is exactly the same as C398 in new prices. But because there is a zero in front of the C398 then the formula does not recognise that the two are the same.
Figure Out How To Sum The Products Purchased In A Given Week
Aug 4, 2009I have a table that has 3 columns:
Product name (one of 15 products I have)
Purchase Date (converted to excel datevalue).
Week number (a number between 1 and 52)
I'm trying to figure out how to sum the products purchased in a given week.
Calculate Periodically Sales For New Products
Nov 4, 2005I'm trying to calculate periodically sales for new products, which have been in the market for max 6 monts. After that 6 months the sales of the product is not to be calculated. I have a huge amount of products, where this information should be calculated, so manually calculating is not an option. The products are in rows, and periods are in columns. As the data concerns several years data there is a problem, that some products have in some months zero sales, and in the next month again some sales. This messes up always my calculations. How to truly take only the first 6 months, and leave all the rest uncalculated?
Sum Of Products Of Cells In Multiple Worksheets
Jan 20, 2007I have multiple sheets in a workbook, and a want to sum the multiplication of two cells on each sheet on a total sheet. i.e. totalsheet!A1 = sheet1!A1 * sheet1!B2 + sheet2!A1* sheet2!B2....+ sheetN!A1*sheetN!B2
I need to do this for hundreds of cells, so mutiplying them on each sheet is not an option. There are enough sheets that writing out the brute force equation as above is too long. I know you can do total!A1=sum(sheet1..sheetN!A1) to add all sheet A1's together, but multiply and sum?
How To Show Monthly Performance Based On Products
Mar 4, 2014What I'm trying to do is show monthly performance based on products of a few different areas. Where I'm running into problems, is with the month listings. If the report shows one month at a time, I can get it to work with SUMIF formulas, however when there is more than one (and there will always be three).
View 6 Replies View RelatedReturning List Of Products From Multiple Entries?
May 1, 2013I am doing a list which has the same products returning several times, but with different values. Want to filter/make a new list, with only one of each product and the summed amount of that specific product. Summing the specific amount is not that big of an issue, but the creation of the list is, least in a smart way i have tried this:
[Code]......
This being the last possible entry for the summed list.
My problem is that the formulas is getting too big for my computer :S, since this formula is copied more or less 10 times.
Here is an example of what i want: Product list.xlsx
Stock Lists - Calculating Combinations Of Products
May 11, 2013Here is what i have so far: stock.xlsx
Basically, i have 2x base products but we can interchange the parts between the sets so we have 1 variant per base product.
But what i want to calculate is how many sets i can make out of the available units that are in stock.
Match Products With Correct Image File?
Aug 4, 2014So I have a spreadsheet of several thousand products (spare parts) and we received a folder of image files to go with these parts, however the file names aren't all uniform; some of the image files have the Part Number, while others use the SKU number. I have cells with the part # and SKU number for each product, and I could probably copy and paste the list of all image file names from the folder into the same spreadsheet. Is there a macro that could search the image file list for either the SKU or Part Number, and whichever is listed for each row, insert that file name into a cell? So I have this:
Item name SKU # Part #
Spare Part 1 123456 P0459381B
Spare Part 2 340934 P2394093A
etc
And then I have the list of image files in its own Sheet or whatever's easiest
P0459381B.jpg
340934.jpg
etc
So after the macro is complete, I'd have:
Item name SKU # Part # Image file
Spare Part 1 123456 P0459381B P0459381B.jpg
Spare Part 2 340934 P2394093A 340934.jpg
Combine Similar Products And Put Into Correct Columns
Jun 21, 2007Try this on for size...
On the MSP sheet is a list of products with a weeknumber and quantity.
I need to combine products that match in the same week and add their quantities.
Then I need to put the quantities into the VOLUME worksheet.
Any products that arent listed need to be added on the bottom.
Recogising Various Products In 1 Column + Returning In Or Out Of Stock In Another
Apr 16, 2008I want to produce a spreadsheet which automatically tidies up a transferral of data from a programme called sage in to a customer service spreadsheet for my company.
I am a bit of a novice but a keen one so please be patient! I do know what I want to achieve in the long run!
The problem I have at the moment is recognising products in one column and getting it to return the value in or out of stock in another. So far I have got it to recognise one product and return the correct statement (using the "IF" function) but i need it to do it for the whole column and for a range of products.
Adding Rows And Products Based On Counta
Sep 16, 2013I am trying to write a VBA code that does the following: The table below provides a sample of the Source (i.e how we start off with) and the Result (i.e how the format needs to look like). More specifically, for each product in 1st column that has a number in blue and or red column i need a row inserted immediately below (hence the counta function to indicate how many empty rows to insert). The end result should have a separate line for each blue/red colour depending if there is a number shown in the source data. Obviously the result should be displayed in another sheet (i.e sheet2).
PRODUCT
BLUE
RED
COUNTA
PROD A
9902
1
[Code] .......
Search From List Of Products Within Text String
Feb 18, 2008i have been trying to find in C1 the 1st product in a list of products that is in text strings of various lengths and the product will be in different places each time eg; list in A1:A4 is babydoll camisole corset basque, the text string in B1 is Satin strech babydoll with low neck
in B2 the text string is Beautiful lace basque with corset style back.
in C1 i'm looking to find babydoll from B1 in the list A1:A4
in C2 i'm looking to find basque from B2 in the list A1:A4
in D1 i'm looking to find any 2nd match from B1 in the list A1:A4, result = ""
in D2 i'm looking to find any 2nd match from B2 in the list A1:A4, result = corset
in E1 & E2 i would like to find any 3rd matches
i have been reading up on this and trying formulas but i cant figure out how to find from a list and the text string data is not in a standard order.
Filter Data Between Two Dates With Customer ID And Sum Similar Products
Apr 1, 2013I have problems with my project. I want to filter data between two dates with customer ID and when click Export Report button , result will copy on next sheet ( sheet's name is report in my file attachment ) and auto sum similar products.
eExample_report.xlsm
Sum Of Products With Non Parallel Arrays With 1 Array In Reverse Order
May 31, 2014I need to calculate a Sums of Products with unusual requirements. Please refer to the attached picture. The Orange Cells under "Noise Weight" are the Formula Cells. The Values in those Cells need to evaluate as shown in the equations below. I want this to be dynamic, so that if I expand the FM Region (Add Columns between "Noise Weight" and "FM15") AND expand the Severity Region (Add Rows between Severity and Noise Weight), I will not have to update Formulas manually. I know for sure that the standard SUMPRODUCT Function cannot handle this.
PRODUCT 1 = D29*E31 + D28*F31+D27*G31.....+D15*S31
PRODUCT 2 = D29*E32 + D28*F32+D27*G32.....+D15*S32
PRODUCT 3 = D29*E33 + D28*F33+D27*G33.....+D15*S33
...
....
PRODUCT 7 = D29*E37 + D28*F37+D27*G37.....+D15*S37
Filtering Highest Revision Number For Individual Products
Apr 16, 2013What's the easiest way of filtering a list of products to only show the highest revision for each product, preferably without using macros or VBA?
E.g. I only want to show the asterisked rows in the table below:
Product Rev
AAA 1
AAA 2
AAA 3*
BBB 5*
CCC 3
CCC 6*
DDD 1
DDD 4
DDD 7*
Potentially, other (random) data could also exist in the same row.
The example above is sorted alphanumerically but a solution for an unsorted table would be even more useful.
Using VLookup To Sort Through A Sheet And Only Return Products Actually Ordered
Jul 20, 2014I have a large Item sheet that also functions as an order sheet so I want another sheet to return only items actually ordered. What is the correct vlookup formula for this situation?
in sheet #1, Column A has the number of orders needed (to be filled in), column B has the name of the product, column C has the price of the product. I want to look through column A after it is filled in and return the value of all three columns only if column A has a value larger than 0 into sheet #2 in the same workbook.
Find Products I Have Ordered And List Them With Their Codes And Quantities
Sep 22, 2007I've created a Excel spreadsheet. In the Spreadsheet I've got one worksheet which has columns and list of codes.
I would like my other worksheet to look through find any products I've ordered and list them with their codes and quantities, creating a summary of just what I want so that I can e-mail just the important bits.
Listing Teams That Have Sold Products - Index / Match?
Feb 28, 2012I have a table with sales teams down the left, and products along the top.
The table shows how many sales of each product a team has made. This is populated from a sales data dump each day.
I need a second table with all the products along the top, and beneath each product, a list of which Teams have sold that product.
do I need some Index / Match style formula here to pickup up each team from the left in the first table that's got a sales value higher than 0 for a particular product?
Count The Percentage Of Occurrences Where Products Are Chosen Togather
Mar 26, 2007What I have is a table (far larger than this example) which details a reference, and then and products under that unique reference. What I need to be able to do is count the percentage of occurrences where product 1 and 2 are chosen together and product 1 and 3 together and so on. So basically I need to be able to see which product options are the most popular choice as a bundle. but have no idea where to begin.
This is an example of my table, but mine has 16,000 rows, so not possible just to count myself.
Creating Database For Daily Production Record Multiple Products
Jun 3, 2013I have a 5 major categories of products e.g. Ketchup, Hot sauce ,mayonnaise , pizza sauce etc.
Now each of these individual categories are further divided into number of of subcategories based upon the the sizes and brands.
I want to create a sheet (One sheet) where I have to record production of each product on daily basis.
Like for example if I am producing ketchup, I select ketchup from drop-down list,Now in next column I have to select production size from predefined sizes i.e subcategories. But it should also be from drop-down menu and it should only show me the subcategories of my selected product like ketchup .I think this has something to do with data validation but I am not sure how this model will work.
How To Compare The Sales Volume Of Identical Products In Two Separate Worksheets
Sep 3, 2013This is how my face looks like at the moment.
Situation:
Worksheet company A: Filled with products
Worksheet company B: Filled with products
Is there a way to copy/export the products from A&B into a new worksheet and compare the sales of these products to eachother?
I want to compare the salesvolumes of the products (of A&B).
Goal of this comparement: See if products need to be moved from A --> B or B --> A.
Hopefuly this is a simple question for some of you, and hopefuly there is a person who will take the time to answer my call...