Combine Rows Based On Account Number/product No

May 8, 2007

I am curious if there is a way to combine the content of rows based on a change in account number(column A). The tricky part of it is that an account number could be in the file once and another could be in the data twelve times. The list is sorted by account number. I want to take the product code and description and keep placing in the combined row for each account number. Attached is a sample of the data.

i need to look up a product based on the number and the qty. see the example attached.

On tab "Printer - S" 1 need to lokup the costs based on the product code 8 and the qty 100. How do i do this and make it adjustable for any range of priduct codes and qtys. Again, becuase this will be used on a Mac Office, i cannot use macros.

I am trying to develop an invoicing system for my company. Most of our customers will have their invoices formatted a certain way which has been relatively easy to solve, however one customer needs to have their invoices formatted in a special way.I have a sheet that pulls in a list formatted like this:

I have two spreadsheets full of products one of new products and the other of old products. I want to filter out the old ones that are discontinued but keep the ones that are still there. The old list has data I want to preserve for the new listings that would be duplicates from the old ones. I want to delete the new listings that already exist in the old list. I'm not sure exactly how to do it. I tried to delete dups based on product id or name but I'm afraid it's deleting entries I want to keep. Both lists contain over 5000 products each. I need to compare two lists and filter out the old products then work on removing the dupes.

Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.

original formula from post 3261273{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}

New formulas needed or revised from the above:

Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)

Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"

Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.

Event DateIDCREATESuspendSTOPPEDUnsuspendElapsed Days (Create to Suspend but NO Stopped)Elapsed Days (Suspend Only, No stopped or Unsuspend)

I searched and searched and I can't find an easy way to do this without using Access which I am rubbish at. Is there an easy way to do the following in Excel.I have several thousand records by row each with a unique numerical identifier. The unique identifier is the "Household". Within the household there are sub "Accounts". The sub accounts are truely unique. All the "Accounts" are in the same column.

What I am trying to do is combine the records into one row per "Household" with the accounts listed in successive columns. The maximum number of accounts there may be is 7 but it can be as few as 1. Example:

I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?

In my second table I have a list of account descriptions. E.g. 'Denis Morgan ZZ500543 leak from bath'

I need to match the account number in Table1 with the correct account description in Table2. I thought a Mid function would be the best option to pick out the account number in Table2? Is this right? How would I go about using it?

I have a huge file with names, addresses and account types & numbers. However, there's a separate row for each account type instead of listing the person's name once and indicating all of their account types on that row as well in separate columns. I've seen several post regarding different Macro codes to use; however, none work for my project since I have several columns that need to be compared beginning on A2:K2 to A24628:K24628. I have attached a small example for your review.

The formatting is set to general, which works for all but the top type of account number. I tried setting to a custom format like "#.#######" ...The only problem with that format is that the "." shows up whether there is a decimal in the number or not. What's the way around that?

I am trying to find the revenue generated by different groups of accounts to figure out which group is worth the most, and rank the others.Here is a attached screenshot of what I am working with, cell BK22 is the revenue of all of the accounts coded AADA but i need to find an easier way other than plugging it in for all 3500 cells.

I have a large workbook that contains hundreds of account codes and values. For example, cell A2 contains the account code "55021357122 Global Business Sales", cell A3 contains code "55021753221 Global Business Purchases" and so on through the column. Column B contains the values.

I need a formula that will sum all the values in column B if the middle 2 number are "21" (positions 4 & 5 in the number). My current range for column A is A6:A366. I've tried SumIFs but that fails on the array because it fails on the MID function.

I would like a formula to add up values in Col E based on a range of account numbers in Col C

For eg adding up Sales values in Col E based on Account number being series 3000-3015 in Col C (this particular series must exclude numbers ending in an alpha). I also have account numbers ending in an Alpha where I need to add up the values pertaining to these for eg 3000D-3015D, 3000K-3015K etc.

I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:

Column BA list of datesColumn C & DNumerical data not used in any calculationsColumn EThe numbers to multiply togetherCell A1Start date to be used in the PRODUCTformulaCell A2End date to be used in the PRODUCT formula

The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:

{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}

This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.

calculate the number of full rows in one data base located on one sheet X to determine how many rows the macro needs to extend on sheet B (sheet B is made only of formulas for data interpretation in sheet A.

I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.

So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.

The drop down list displays the products and can be added to by typing new product names on the other sheet.

What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.

I'm trying to design a Profit and Loss template that calculates off an accounting software data dump. I will attempt to explain the problem here but I have included a summary in the linked workbook which would be easier.

DOWNLOAD WORKBOOK: [URL]

I have two problems:

The Formula: Basically, I need to calculate every cell in the Profit and Loss statement based on the below criteria: The Profit and Loss item accounts, e.g. Gross Income has 6 accounts. There is a reference table for these - so incorporate index match/vlookup? User selected data from 4 combo boxes.

I am thinking a SUMIFS formula (using the combo box values) and somehow combining a vlookup to pull the items accounts. I'm starting to think that VBA is the best method?

Tweaking: The attached workbook is a small example. The full data dump contains 60,000 rows.

I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.

I am looking for an efficient solution to the following problem. I have a sales table with two columns, titled C1 and C2. The first column lists the product sold, and the second column lists the associated customer.

Here's what I mean (though I can't figure out how to create neat columns in this post):

I am trying to put all my parts with quantities on a seperate sheet called "Parts List" Every time you select a quanity for one of the parts, I want it to pop up on my parts list. This will make it easier to identify the exact parts I want and also the quantity I need. This will be much more convenient then scrolling down my parts list and trying to find the one's with quantities.

I think I need to use a vlookup or even a Macro but I don't know how to go about doing this.