Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Reordering a list

Hi,

I'm very new to doing anything overly complex in Excel.

I've been trying for most of the night to do what is probably a fairly simple macro if you know what you are doing.
Its a list used to decide who can have casual time off work.
The idea being the person nearest the top of the list is given first choice at any time off, once they take the time off they move to the bottom of the list and everyone moves up a place.

Basically its a list of names of staff at work, plus a few other cells with things like employee number etc.
I was hoping to be able to do something to make it very simple to use.
I have a column which I'd like to be able to put a value in, a simple 'x' for instance, really just a way of marking which people need to go to the bottom of the list.
Then I'd like to run a macro which looks at column C and loops down the list moving anyone with an 'x' in thier column C at the bottom of the list.
When the loop is finished, clearly the top of the list needs moving back to the top of the page.

I've got as far as a loop which can figure out if it needs to do something for each row, but I'm having trouble getting it to select and paste the data needed.
I understand the logic of how I want to make it work, just can't figure out the syntax to select a specific range with a variable in the row number and paste to a specific row.

I have tried using offset to change cells but I think this is not a tidy way to do it (and could not make it work anyway)


Post your answer or comment

comments powered by Disqus
I have a list of names in cells A2:A16, and a list of numbers from 1 through
15 in cells B2:B16.

In E2:E9, I have five names, each separated by a comma and a space. Each of
the names in E appears in the list in A.

In F2:F9, I want to reorder the names corresponding to the numbers in B, in
ascending order.

Example:

Col A Col B
Steve Blake 1
Mo Williams 2
Lynn Greer 3
Charlie Bell 4
Michael Redd 5
Chris McCray 6
David Noel 7
Bobby Simmons 8
Ruben Patterson 9
Ersan Ilyasova 10
Damir Markota 11
Charlie Villanueva 12
Brian Skinner 13
Dan Gadzuric 14
Andrew Bogut 15

Col. E
Bobby Simmons, Michael Redd, Andrew Bogut, Ersan Ilyasova, Charlie Villanueva
Ruben Patterson, Chris McCray, Andrew Bogut, Damir Markota, Brian Skinner
Ersan Ilyasova, David Noel, Andrew Bogut, Charlie Villanueva, Dan Gadzuric
Damir Markota, Bobby Simmons, Michael Redd, Brian Skinner, Andrew Bogut
Steve Blake, Mo Williams, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva
Mo Williams, Lynn Greer, Ruben Patterson, Damir Markota, Brian Skinner
Lynn Greer, Charlie Bell, Ersan Ilyasova, Charlie Villanueva, Dan Gadzuric
Charlie Bell, Michael Redd, Damir Markota, Brian Skinner, Andrew Bogut

I want to take the each row in col E and reorder it as follows (first row
shown here):
Michael Redd, Bobby Simmons, Ersan Ilyasova, Charlie Villanueva, Andrew Bogut

....so that the names correspond to the numbers in B, lowest to highest:
Redd = 5; Simmons = 8; Ilyasova = 10; Villanueva = 12; Bogut = 15.

Any ideas on how to do this?

thanks,
Bob

Good Evening,

I am trying to create set of excel file with the names from a list. I am starting with the active workbook and asking it to loop until the list is empty

So start at box A8 and create a workbook for each entry on the list as a seperate workbook.

I have the sameple code below


	VB:
	
 Update() 
    ActiveSheet.Select 
    Range("A8").Select 
     
     
    Do Until ActiveCell = "" 
         
        SName = ActiveCell 
         
         
        ActiveWorkbook.SaveAs Filename:"SName ".xls 
         
         
        Windows(SName + ".xls").Close True 
         
        ActiveCell.Offset(1, 0).Select 
    Loop 
End Sub 
 
[SIZE=4][/SIZE] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi everyone! I'm new to the forum, and if I posted in the wrong section, please let me know.
I have the following problem in Excel 2007:

I need to make a table in which i have a dropdown list. For every value that I pick (let's say X), a cell with the formula :
=VlookUp(Data_In_a_Cell;ArrayX;2;False)

So for value 1 of the pick, the cell will modify its formula to : =VlookUp(Data_In_a_Cell;Array1;2;False) ;
for value 2, the formula will be: =VlookUp(Data_In_a_Cell;Array2;2;False) and so on.
Assuming that i define Array1, Array2,.... ArrayX , how do I do that?

I tried using VBA, but it displays me an error, because the code wants to keep the proper value of the cell, but as soon as it replaces, Excel will do its job and input another value of the cell. The code won't work

Next I tried using Replace function, but I don't quite get it...
Please help me if you can.
For any questions, I'm available. See you!

Other forums I asked:
http://www.excelforum.com/excel-general/846992-i-need-to-change-arrays-in-a-formula-if-value-in-a-list-changes.html
http://www.ozgrid.com/forum/showthre...601#post619601

Dear All,

I found this forum very usefull, but I'm stuck on this problem after some google & forum research, so I need your assistance. (no luck with index/match, vlookup, ... so far)

I have a list of projects, each related to a release (see the example table below). As a project can be descoped from a release and moved to another one, we keep the initial line so we have an overview of what has been descoped, but we keep it in a special status.
This means that my file contains a long list of projects where there are multiple lines about the same project.

In another sheet I need to lookup the actual release of a project, for example: What is the actual release of project 101B? (the answer should be R3, but vlookup will return R1).

Can anyone provide me with a formula that will return the correct answer?

Release Status Project ID Project Description R1 Confirmed 101A R1 Descoped 101B R1 Descoped 102 R1 Confirmed 103 R2 Descoped 101B R2 Confirmed 102 R3 Confirmed 101B R3 Confirmed 101C R3 Confirmed 104
Thanks in advance for your assistance,
Fred

Hi

Can anyone help me with copying a list box with selected items into a new worksheet while retaining the selected items.

Notes I'm currently using an active x listbox.

Really struggling with this at the moment any help would be much appreciated.

thanks
Lee

Afternoon all,

I have been searching for some help on here but cant quite find what i need.

I was hoping someone may be able to help me. I would like to be able to insert a date into a cell in run a macro through a button that will match the date and display the information in column C,D,E and F in a list box.

Any body have any idea's i'm very close to gauging my eyes out with a spoon.

Thanks for taking the time to read this.

Mart.

Hi everyone,

Anyone knows how can I create a list of values out of a single cell? For example, If I enter a value in cell B1 it is captured in cell A1, but then if I enter another value in B1 it is captured in A2 and the value in A1 is still there, and the next value entered in B1 is captured in A3 and so on

Thank you!

In A1, I have a list that looks like:

Notebook
Pencil
Paper

I would like to make it appear in B1 as:

1. Notebook
2. Pencil
3. Paper

Please note that each of the two lists are in separate single cells (A1 and B1).

Is this possible? Thanks.

Eugaul

Hi all, I have a list of contact info which i need separated a certain way.

right now everything is in column A like this

New jersey

xyz company
john doe
111 mystreet rd
happyplace, NJ 11111
USA
phone: 555-555-5555
Fax: 555-555-5555
email: johndoe@jd.com
web: www.xyz.com

[map it]
xyz company
john doe
111 mystreet rd
happyplace, NJ 11111
USA
phone: 555-555-5555
Fax: 555-555-5555
email: johndoe@jd.com
web: www.xyz.com

xyz company
john doe
111 mystreet rd
happyplace, NJ 11111
USA
phone: 555-555-5555
Fax: 555-555-5555
email: johndoe@jd.com
web: www.xyz.com
xyz company
john doe
111 mystreet rd
happyplace, NJ 11111
USA
phone: 555-555-5555
Fax: 555-555-5555
email: johndoe@jd.com
web: www.xyz.com

etc.....

some have 1 space, no space, or it says [map it] between the contacts also there is a break of 1 line, state name then 1 line. as the list i found was on a webpage and i copied it from there as it showed and pasted it to excel.

i need to import this list into act 2012 as a csv which isnt an issue i can handle that part. the part i cant handle is breaking it down so i can do that.

I need it to look like this:

company first name last name street city state zip phone fax email web xyz co john doe 111 whatever some city some state 11111 111-111-1111 111-111-1111 johndoe@xyz.com www.xyz.com

I appreciate any help on this!!!

Hi All,

I have a list of values that all begin with a letter, "P" or "S". I need to find the biggest "P" value and the biggest "S" value.

For example, in the following list:P12345S23456
S23457
P12346
P12347P12347 is the largest "P" value, and S23457 is the largest "S" value.

Any suggestions about how to find these values? It can be two different formulas...

Much appreciated, thanks in advance!

Dear Gurus,

Need some help with this issue. I have a list of vendors who enter their sales data in an excel spread sheet. I need to isolate the top four and create a separate list using excel formulae.

Vendor - 1 100 Vendor - 2 2000 Vendor - 3 3000 Vendor - 4 4000 Vendor - 5 5000 Vendor - 6 6000 Vendor - 7 7000 Vendor - 8 8000 Vendor - 9 9000 Vendor - 10 10000
Out put should include :
Vendor - 10 10000 Vendor - 9 9000 Vendor - 8 8000 Vendor - 7 7000
Thank you,

Regards,
Ravi.

I am trying to create a program that lets a user input a list of numbers (ex. 140,145,150,140), have the program write this user inputed data into a .csv file, then read the numerical data from the .csv file and put it into a spreadsheet.

The main thing I am having trouble with is to make it so that the program can read each number the user inputed and designate that to a variable...or is that even possible?
The only other methods I can think of is making an InputBox command and making it so that "Type:=64", so that it is an array (since it is a list of numbers); however, I am not sure how to do that without it always giving me an error.

The help is much appreciated.

Hello,

I want to use a list box to show certain sheets in my workbook. Once selected I'd like a command button to create that specific new sheet.

Thanks you!

Hi there,
I am applying for a job and they gave me a rather complicated excel quiz and I am stuck... they want me to extract the first three numbers only from a list of phone numbers in a new column... Seems simple, just use the mid function. However, each phone number is differently entered such as some have parenthesis in front of the first three numbers some don't etc... What function can I use to achieve this?

It looks like this and I need to extract the first three numbers i.e. area code from each in a new column... how do I do this while eliminating unwanted characters from certain numbers that have parenthesis in front of it?

Client Phone Numbers
(817) 424-6099
624-863-2945
8546826059 ext.682

Any help would be appreciated. Thanks so much!

Hello, I received 0 help at another forum (sad really) Google provided some guidance, but I still can't get this stuff to work for me.

I have a list of files from c2:c9 on a worksheet.

I need a macro that can open one at a time copy a certain range (named MKTG) from each file and paste it, then close it, then open the next, etc.

Here are some current codes that I am trying to tweak...


	VB:
	
 Workbook 
 
ExtFile = Range("C2") 
 
If Not ExtFile = "" And Dir(ExtFile)  "" Then 
Else 
    ExtFile = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Please Select Service A
File") 
End If 
On Error Resume Next 
Set ExtBk = Workbooks(Dir(ExtFile)) 
On Error Goto 0 
If ExtBk Is Nothing Then 
    Application.Workbooks.Open ExtFile 
    Set ExtBk = Workbooks(Dir(ExtFile)) 
End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
        ThisPath = ActiveWorkbook.Path        Workbooks.Open FileName:=ThisPath &  "" & Range("A1").Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


what i a trying to do is,
MY FIRST PAGE LOOKS LIKE THIS:
A B C D
Week number Week 01 Week 02 Week 03 From Date 2-Jan-12 9-Jan-2012 16-Jan-12 To Date 8-Jan-12 15-Jan-2012 22-Jan-12
LIST HERE

I HAVE 4 OTHER PAGES THAT HAVE INFO ON THEM.
PAGE 2
A B C D E F G AC-111 4 15X20X1 25-Apr-2012 AC-258 3 16X25X2 5-MAY-2012 3 20X25X2 WHAT I NEED TO DO IS IF PAGE 2 AL COLUMN IS IN BETWEEN PAGE 1 ROW 4 & 5 ,TO MAKE A LIST FROM PAGE 2 COLUMN G ON PAGE 1 UNDER THE CORRECT COLUMN ON PAGE 1. HOW I EXPLAINED THAT CORRECTLY. THANKS FOR THE HELP. SEE ATTCH. HOPE THIS HELPS. SORRY

Loop through a list in sheet 1 and look for all partial matches in sheet 2, then return the data from same row, different column into one cell per search term separated by a carriage return (vbLf) on sheet 1.

See the attached sanitised and shortened LookupTestExample.xls

Sheet “COMP XRef” contains the data to be searched in Col A and to be returned in Col F

Sheet “Reports” contains the terms to be searched for in Col D and expected results in Col F.

I need to loop through each term in Reports!D3:D51, look for ALL partial matches (not blank) where the COMP Xref!A6:A1466 starts with the data in Reports!D3:D51. Then place that information in one cell per search term (Reports!F3:F51) separated by a carriage return (vbLf).

Clear as mud? Thanks in advance.
LookupTestExample.xls

I have made a sheet for a tournament we run in our club as you can see there is a list of 32 possible players but this is not set in stone (that is the maximum of players) some weeks they may be only 17 or 26 I don't know until the day.What I am trying to do is randomly pick a name from the list and place it into the different groups But I want it to place 1 name in each group first then a second name in each group before filling out the third name in each group and so on until we run out of players.This ensuring that everybody gets a game but some groups may have more players than others.I can do the random part but I away's get repeats so I am stuck.I have uploaded a sheet for you to see what i am trying to do any help would be great.

Hello!

I have a inventory report that several people run several times a day, to help simplify the process I want to have the report separate out the data to inventory groups so that it doesn't have to be done manually wasting valuable time.

I have six worksheets currently, with the potential of adding a few more.

Location - A list of locations
The columns

Mez Zone 1 (Column A)Mez Zone 2 (Column B)Mez Zone 4 (column C)Re-wrap (Column D)Cage 8 - an inventory area (Column E)Cage 4 - an inventory area (Column F)Multi - an inventory area (Column G)South - an inventory area (Column H)
Paste sheet - raw data is pasted here from a emailed report that is then formatted

Mez Zone 1-3, Rewrap, Cage 8, Cage 4, Multi, and South - different areas of inventory.

I added some sample data with private data edited. The goal is to get anything listed under locations to be moved to the appropriate sheet.

The data on the Paste Sheet does not need deleted.

Location Sheet looks like:

Upstairs (Delete) Cage 8 Cage 4 Multi South EX-A-0 N-BA-1 N-EX-0 NM-A-0 N-A-0A EX-B-0 N-BG-1 N-EX-1 NM-A-1 N-A-0B EX-C-0 N-BH-0 N-EX-2 NM-B-0 N-A-0C A-A-0 N-BN-1 N-AG-0 A-A-1 N-NN-0 N-AG-1 A-A-2 N-NN-0 A-A-3 N-NN-1 AA-A-0 N-VV-4 AAA-A-0 N-VV-5 AAA-C-1 N-XX-0 AA-B-0 N-XX-5 AA-B-2 N-YY-0 B-B-0 N-Z-0A

Paste Sheet looks like:

Location Piece Dest Found? Scanned Status N-AG-0 0.824874011 0.142021184 1 Good N-AG-1 0.923136055 0.634770811 1 Good N-AG-1 0.105232206 0.497392571 1 Good N-BA-1 0.641357529 0.532030334 1 Good N-BB-1A 0.196185717 0.657255508 1 Good N-BG-1 0.938708236 0.205436865 1 Good
This was posted on MrExcel.com with no replies.
http://www.mrexcel.com/forum/showthread.php?t=617494

If more information is needed I will provide.

Hello,

I was hoping someone would be able to help me with this formula since we are now using excel 2010 the With Application.FileSearch no longer exists and I am not sure on how to fix this formula now.


	VB:
	
 CREATEPDFDIRECTORY() 
    Dim path As String 
    Dim f As Integer 
    path = "INSERT FILEPATH HERE!" 
    f = 1 
     
    With Application.FileSearch 
        .LookIn = path 
        .FileType = msoFileTypeAllFiles 
        .Execute 
        For i = 1 To .FoundFiles.Count 
            If UCase(Right(.FoundFiles(i), 3)) = "PDF" Then 
                Range("A" & f).Value = .FoundFiles(i) 
                ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & f), _ 
                Address:=.FoundFiles(i), TextToDisplay:=Split(Dir(.FoundFiles(i)), ".")(0) 
                f = f + 1 
            End If 
        Next i 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This formula used to create a list of hyperlinks from a folder (pdf format)

Thanks

Hello Everyone:

I have a form that consist of a list box and a text field. The list box's properties has a column count of 2 with linked cell C5. The data comes from name range: =eebox!$A$2:$B$25000

The box gives me an employee ID in column 1 and the corresponding employee name in column 2. However, this has about 200 names and therefore I would like to add a search feature for the client to use. I want to search by name. however, when I put in a text field to perform the search I can only link to cell C5 where the ID is and my list box is linked to. Unfortunately, it is unlikely that the client would have this ID, they would want to search by last name.

Is there a way that I can search by employee name in column 2 and as I am typing the last name, can the box begin to show the last names as I put in letters? Do I have to use a text box or can it be done on a combo box? I am open to changing if necessary.

THanks

Hello! I need some help from you. Please help me to generate a list of numbers of this function in Excel: COMBIN(20,3), but i want the combinations of numbers. I want to creat a list of 20 questions and every question has to answer. I think that are 1140 combinations. I'm right? I am a newbie and not so advanced in excel. Thank you!

Thanks for your help in advance. I searched for an answer but came up shy. I need a way to send my data to 2 locations but in different and changing locations. The code in red will not change its location. The second code however will need to look for the (sheetname) then find the (sheetname) in a list and update the cell to the right of it from a userform. Any help is apreciated. Thanks alot


	VB:
	
 
Dim sheetname As String 
sheetname = UserForm12.ListBox1.Value 
Sheets(sheetname).Select 
 
 'This Code Puts The Name In A Pre Destined Location
[COLOR=red]Range("A6:a1500").SpecialCells(xlCellTypeBlanks).Cells(1).Value = UserForm12.ComboBox1.Text 'Name[/COLOR]
[COLOR=red]Range("b6:b1500").SpecialCells(xlCellTypeBlanks).Cells(1).Value = UserForm12.ComboBox1.Text 'Date[/COLOR]
Sheets("Stock").Select 
 'Find the sheetname in the named range called (RS) then add the contents of textbox3 from my userform into the matching
adjacent cell.
Sheets("Home2").Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So if the listbox1.value is Apple and the value of textbox3 is 1. And Apple is found in cell A5, then 1 would be placed in B5

Hi,

I've been trying to do the following:

I have a list of machines and i have a long list of services for those machine. Each machine has a contract start date. I want to find the first service (i.e. service entry with the lowest date value) that comes AFTER the contract start date

I tried a couple of ways to no avail, any help is greatly appreciated! See the example attached and thanks for any help!

Ta

Also posted on excelforum http://www.excelforum.com/excel-gene...html?p=2871663
J


No luck finding an answer? You could always try Google.