Now the list is, if the quantity column for the "Productx" is >=1 than whole row will add to the "Order", if not skip it, i have the formula in the "Order" block A2.
i will have about 6-7 list for "product", now in my file is only 2 Product list (Product1, Product2) and now i want to add more for this, like Product3, Product4....... so i think i need to add program for this and also some expert user also tell me need to add VBA for this.
I have an Excel 2003 list with four columns as shown below:
Zipcode
CRRT
Count
Bundles
85710
C004
693
14
85710
C005
867
18
85710
C006
1021
21
I want to "expand" this list to use in a Mail Merge program to produce tags for our direct mailing. Each bundle contains 50 letters, so in the first line on the above spreadsheet, there will be 13 bundles of 50 and 1 of 43. Currently, I can cut and paste to create the following table, but there has to be an easier way...
Zipcode
CRRT
Count
bcount
Bundle
ibundle
85710
C004
693
50
1
14
[Code] ........
So as I pull this into a mail merge I will get a tag that shows the zipcode, the crrt, the total pieces for that crrt, then number of the bundle for example " 1 of 14" and the quantity in that bundle, for example "50 of 693", then the next bundle tag will print, 3 to a page. I know that here has to be an easier way either in the mail merging process itself or with EXCEL? Am even open to build these with ACCESS to create the tags. The beginning database contains anywhere from 350 to 800 lines of original data, so as you can see the cut and paste is VERY time consuming.
How do I list selected data by order of appearance in a list?
Suppose I have a named range somewhere in my workbook called "TeamsRanked".
This range contains hundreds of names. The data associated with each of these names appears in a table in the workbook in rank order. The teams are ranked in descending order according to points scored. This means that the ROW() for the HIGHER-Ranked teams will be LOWER than the ROW() for the LOWER-Ranked teams. Also, this named range is constantly being resorted as updated data is introduced.
Now...Suppose I want to monitor just a few of the names??
For example: Cells A1:A3 on a separate worksheet contain the names of the teams I want to monitor.
What formula do I need in Cell A5 to return the HIGHEST ranked of the teams in Cells A1:A3??
...in Cell A6, to return the next highest ranked.
...in Cell A7, to return the LOWEST ranked.
I tried some IF(ROW(MATCH...)) ---"is less than" ---IF(ROW(MATCH...)) formulas...and I keep getting INVALIDs in my formula palette.
I need to have 6 rows automatically adjust themselves, and be listed in descending order, according to a percentage that will change daily.
View 9 Replies View Relatedget sorting out top 3 clients with the total amount.
A
B
C
1
Company Name
Value
2
Apple
2100
3
Mango
4000
I found that =LARGE only work for single cell name with single value instead of multiple cell that may contain same name but different value and add them up.
I have a worksheet with data in several rows. Column wise they consist of names, points etc..
I am trying to setup a formula whereby the formula looks at the data in the points columns and then re-orders the rows accordingly taking all the information in the row with it, with the highest points total at the top and lowest points total at the bottom.
Each points cell, has a formula in there already which pulls the points total from other cells and combines them.
i have a excel database made up of about 5000, points of interest every thing from nightclubs, pubs police stations, restaurants, etc all in alphabetical order A TO Z
my database read like this
COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E COLUMN F
POINT NAME ADDRESS POSTCODE TYPE DETAILS ONEWAY ST
odeon cinema leicester sq WC2 cinema lve on left no
COLUMN G IMFOMATION
lve cinema on left this is next to the raddison hotel
what i would like to do is change the order so that every thing is listed in post code order W1 W2 W3 W4 EC1 EC2 EC3 EC4 WC1 WC2 etc so that i can search in pacific post code
suppose when i add 1 in the quantity, it will appear on the "Order for EMS" and "Order for FEDEX" but now they show #NAME?, what's wrong, it's not happen before, why?
View 14 Replies View RelatedI have a list of 20 random numbers in Column A, what I need is a list to be compiled in Column B showing the highest as 1 and lowest as 20.
A B
2345 4
123 5
3568 3
9732 1
4325 2
This totals change hourly. Dont know if this requires a macro or just a formula in Column B
I have a list of names and they are in the following format :
A1 B1 C1
A2 B2 C2
A3 B3 C3
A4 B4 C4
A5 B5 C5
How can i list them in the following order ?
A1
B1
C1
A2
B2
C2
A3
B3
C3
This is quite a long list containing about 5000 names in total.
I have a question on paste special. Is there a way to paste special reverse the order of numbers?
I have data going in reverse chronilogical order vertically on my spreadsheet. So for example,
3/1/07 12.3 ( Cell A1)
2/1/07 13.2 (Cell A2)
1/1/07 14.5 (Cell A3)
12/1/06 16.7 (Cell A4)
I want to now paste this data vertically (so transpose which there is a handy check off box for) BUT ALSO to Reverse the data so vertically it now reads :
16.7 (in cell A1 for ex) 14.5 (In cell B1 for ex) 13.2 (In cell C1) 12.3 (In cell D1)
Is there a way to paste special reverse the order of numbers?
formula getting an ascending order from a long list of numbers.
I use excel for mac, 14.3.9
I have the below code that sorts a list of Doors that I have in row C17 downwards. Door 54, Door 7, Door 109 etc. The list is feeding a drop down box, people find the door they were looking for, in the said drop down box.
Code:
Sub ListSorter()
Dim LastRow As Long
LastRow = Range("C" & Rows.Count).End(xlUp).row
[Code]....
I wondered if there was some code I could add to the end of this, that would the sort worksheets, which are all named after each cell in the list, in the same order.
i have two date lists
list 1 dates a1:a100
list 2 dates b2:b100
i want to extract the date from the two lists mentioned above in on list start from c2 taking in consideration the dates to be in sorting from A-Z when it extracted in the one list
i need formula not a macro
I have this formula below it will list uniques from column A but I would like it return in a alphabetical order. Is there a way to add that condition in the formula below a all in one formula?
=INDEX($A$3:$A$23,MATCH(TRUE,ISNA(MATCH($A$3:$A$23,C$2:C2,0)),0))
Example:...
I order sushi at work for the office, and I want to automate the reporting side of it
this is what the information looks like (bad formatting, sorry) (the notes column is also void currently)
date name order # decscript Price Paid? Notes 1/01/2009 John Smith 10 blah 10 5 Y
Jane Smith 12 blah 12 7 Y
Adam Black 1 blah 1 4 Y
Charles Dee 1 blah 1 4 Y
15/01/2009 John Smith 10 blah 10 5 Y
Jane Smith 12 blah 12 7 Y
Adam Black 1 blah 1 4 Y
Charles Dee 1 blah 1 4 Y
Handel Fee 6 blah 6 8 Y
Gretal Goo 7 blah 7 6 Y
1/02/2009 Adam Black 1 blah 1 4 Y
Charles Dee 1 blah 1 4 Y
Gretal Goo 9 blah 9 7 Y
What I enter is the 1) person's name 2) their order # and 3) wether they have paid (Y/N).
The proper version has a vlookup to fill in the description and price.
What I want to do (but don't know how) is to automatically count the number of orders each week, and the value of each week's orders.
Well, actually I can do the count orders/sum value manually - more specifically I can't work out how to automatically seperate each week. The main problem being there's always an unknown and variable number of orders per week.
One solution is to have a fixed number of orders per week which _sort of_ works, but is clunky and inelegant. Plus the names don't autocomplete then as it has blank spaces.
I Have A List That Is Generated From Another Program Dump Info Into Excell.
This List Has Names In One Colun And Corresponding Values In The Next Column. My Goal: Get The Name And Values To Be Listed In A Specific Order To Be Copied To Another Workbook. Issue: All The Names Do Not Always Get Sent From Original Program. Example:
Person1 Always Needs To Be In Row2
Person 2 Always Needs To Be In Row3
Etc
However Person1 May Not Be On The List Today. In Which Case I Need Row2 Blank
The user will be pasting a list of names in Column C and a list of Names in Column F I then want in Cell M1 for example a List box with all those names in from the 2 columns in alpha order.
I can create a hidden column that has the full list of names from both column and have a data validation drop down from that but cant think how to get it into alpha order in that drop down box without having a macro they would have to run 1st to sort it which i dont really want to do. is there a way if ranking a list of names in alpha order as then i could do then do a look up int he correct ranking order and use that list
I have several series of 13 figures (from 0 to 20), such as :
000141833000001
I would like to filter those series with the following condition :
Each figure (except the zeros) should be higher or equal to the first figure (found on the right) which is not a zero.
In the example :
14 should be higher or equal to 18 : FALSE
8 should be higher or equal to 3 : TRUE
3 should be higher or equal to 3 : TRUE
3 should be higher or equal to 1 : TRUE
So, the serie is FALSE, due to the fact that 14 is not higher than 18.
I have been sent a price list from a supplier and need to create an order form that can be populated from the price lists. I have to pages of price list as well. I will also be adding to these as I start getting other supplied product and need my client to be able to generate a order from these in the simplest way possible. Again I hardly use excel and these docs are all sent in excel format.
View 4 Replies View RelatedI need to change the list of names in a column to another area but in the reverse order, not in sorted order (eg. I have list of names in A1:A10 and want to make a reverse order of this list in another area).
View 2 Replies View RelatedHow to List files from a folder down a column in reverse order?
View 3 Replies View RelatedLooking for a dynamic macro to reorder columns in a worksheet from a list of column headings Column A in another worksheet.
View 3 Replies View RelatedI am reposting this because I do not think I did a very good job explaining what I am trying to do.
I am at a total loss on how (if it is even possible) of how to do this. So what I have done is tryed to break down each step. If someone could even get me started in the right direction many of the steps are redundant and I could work on that part myself.
I am attempt to create system to match parts that are alike in a single project, so I can create a material ordering list. This is just one step in the process (the hardest one) I will take the returned data and use it further in the process to create the actual material list. I have 2 worksheet of Data "PartsNeeded" and "PartsAvailable" with a 3rd sheet "PartsFilled" as my report Sheet.
Attach is the sample data along with a "notes tab"
- I have a list of 8 digit numbers in Column A sorted small to large
- Some of the 8 digit numbers are duplicated.
- In Column B I would like to have more of a combination of countif and listing the duplicates in order (1 for the first dup in the column, 2 for the 2nd, etc.)
Example of what I'd like to see
Column A Column B
123456781
123456782
132546841
685036541
985413561
985413562
985413563
As you can see if the number in column A is on the list one time column B would show a 1If the number is shown two times (the first time it shows up will show a 1 the second will show a 2) If the number is shown 3 times (the first time it shows up will show a 1 the second will show a 2, the third tiem would show a 3)
I have a list of numbers in cells A1:A10, these numbers change frequently. I would like to set up a formula to calculate the 3 lowest values from those numbers and list the three lowest values in cells J1:J3, with lowest value on J1 and the highest of the 3 values in J3.
I know I can use the small/large functions for this, but im not sure how to work it out to use just the 3 lowest values.
I'm trying to get this formula to work across multiple worksheets. It suppose to list names in alphabetical order. I'm using these define names to get it to work. Also may I have another formula that list unique names in alphabetical order?
Col_A=T(INDIRECT("''"&XWSLST&"'!A"&(2+MOD(S,N))))
N=50
S=ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1
WSLST=Sheet6!$D$2:$D$5
=IF(ROWS($E$2:E2)<= COUNTIF(Col_A,"?*"),INDEX(Col_A,MATCH(LARGE(COUNTIF(Col_A,">"&Col_A),ROWS($E$2:E2))=S+1,COUNTIF(Col_A,">"&Col_A),0)),"")
I have a sheet which contains a list of products. I have created a check box next to each name with the linked cell in the adjecent column. I have formatted the cell so you can see the TRUE or FLASE.
I need to be able to select different products and then when I run a macro, it places all of the selected products onto the second sheet. I just need it so it lists the products in column A with no blank rows.
Can this be done using Index/Match? Would a macro which achieves all this be possible?
My problem is to create a macro that will list all folders, subfolders and files in an order in excel. So that I know which folders and files belong to which main folder, more like a hierarchy.
For example,
Sourcefolder
Subfolder 1
File1
I have code that list all files in folders and subfolders, but it does not put them in a hierarchy fashion.
I looked at threads realted to printing but wasn ot able to find something similar to what I need. So here is my problem:
I have a file with more than 100 worksheets (each sheet contains the invoice for one store). I would like to create a macro that would enable me to determine the order in which worksheets would be printed. How to do it?
Idea #1: the printing order would be based on the value in cell L1 that would contain the route number for each store. Stores belonging to the same delivery route will have the same value in L1. So, the macro should first print all sheets with 1 in cell L1, then print all sheets with 2 in cell L1 and so on...
Idea #2: Creating a separate data sheet with the list of all stores and their corresponding route number. Let's say info is contained in range A1:B150, where Column A contains the name of the stores and column B contains the route numbers. The macro then should look at that list to determine the printing order of the subsequent worksheets (the name of the store in column A would be the same as the name of the worksheet corresponding to that store).
Idea #3: sorting my 150 worksheets manually. It does not solve my problem fully, though, because stores do not always belong to the same route. So the manual sorting should be carried out daily and would not save time at all.