Compare Names in a column and create single list with adjacent vla

Good Morning,

Here's the challenge the information shown below is on a single worksheet. 
What I need to do is create a single column of names from Columns A,C,E,G. 
The adjacent columns B, D, F, H contains values associated with each name and 
need to be added next to the proper name in the new list...Below I added what 
is looks like currently and how it need to look after run the macro or VB 
script.  

Col A      Data1    Col C        Data 2     Col E      Data 3   Col G     
Data 4
Name1    0.3       Name1      100%    Name 1     15      Name1     0.1
Name2    0.5       Name2      10%      Name 2     12      Name2     0.2
Name3    0.7       Name3      90%      Name 4     18      Name4     0.3
Name4    0.7       Name4      90%      Name 5     18      Name5     0.3

Below is how the result should look

Col A      Data1    Data 2     Data 3     Data 4
Name1    0.3        100%       15          0.1
Name2    0.5        10%         12          0.2
Name3    0.7        90%      
Name4    0.7        90%         18          0.3
Name5                                18          0.3

Thank You in Advance for any assistance..
Respectfully,
George
0
Utf
4/26/2010 3:29:01 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
854 Views

Similar Articles

[PageSpeed] 45

This is very similar to a request I did on Sunday.  I modified the code
from Sunday below.  Here is the link to Sundays request
http://tinyurl.com/33sz3mj


Sub LookupNames()

'put names into column IV
'then use advancefilter to put names at bottom
'of worksheet

'use data in column A to get Last Row
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'put final list 5 rows down from last date
NewRow = LastRow + 5

'put header in IV1 so advance filter doesn't create duplicate entry
Range("IV1") = "Unique Names"
'copy first set of names in column B to column IV
Range("A1:A" & LastRow).Copy _
Destination:=Range("IV2")
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'Copy Second List of names in column D to column IV
Range("C1:C" & LastRow).Copy _
Destination:=Range("IV" & (LastRowNewData + 1))
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'Copy third List of names in column F to column IV
Range("E1:E" & LastRow).Copy _
Destination:=Range("IV" & (LastRowNewData + 1))
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
Range("G1:G" & LastRow).Copy _
Destination:=Range("IV" & (LastRowNewData + 1))
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'use Advance filter to move copy data
'put Data 1 starting one row below NewRow
Range("IV1:IV" & LastRowNewData).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("A" & (NewRow - 1)), _
Unique:=True

'delete temporary data in column IV
Columns("IV").Delete

LastRowUnique = Range("A" & Rows.Count).End(xlUp).Row
'Unique names goes from NewRow to LastRowUnique
'=IF(ISERROR(VLOOKUP(A10,A$1:A$4,2,False)),"",VLOOKUP(A10,A$1:A$4,2,False))
'=IF(ISERROR(VLOOKUP(A10,C$1:C$4,2,False)),"",VLOOKUP(A10,C$1:C$4,2,False))
'=IF(ISERROR(VLOOKUP(A10,E$1:E$4,2,False)),"",VLOOKUP(A10,E$1:E$4,2,False))
'=IF(ISERROR(VLOOKUP(A10,G$1:G$4,2,False)),"",VLOOKUP(A10,G$1:G$4,2,False))

Lookup1Str = "VLookup(A" & NewRow & ",A$1:B$" & LastRow & ",2,False)"
Lookup2Str = "VLookup(A" & NewRow & ",C$1:D$" & LastRow & ",2,False)"
Lookup3Str = "VLookup(A" & NewRow & ",E$1:F$" & LastRow & ",2,False)"
Lookup4Str = "VLookup(A" & NewRow & ",G$1:H$" & LastRow & ",2,False)"

Range("B" & NewRow).Formula = _
"=IF(ISERROR(" & Lookup1Str & "),""""," & Lookup1Str & ")"
Range("C" & NewRow).Formula = _
"=IF(ISERROR(" & Lookup2Str & "),""""," & Lookup2Str & ")"
Range("D" & NewRow).Formula = _
"=IF(ISERROR(" & Lookup3Str & "),""""," & Lookup3Str & ")"
Range("E" & NewRow).Formula = _
"=IF(ISERROR(" & Lookup4Str & "),""""," & Lookup4Str & ")"


'copy formula down column B for each unique name
Range("B" & NewRow & ":E" & NewRow).Copy _
Destination:=Range("B" & NewRow & ":B" & LastRowUnique)

End Sub


-- 
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198519

http://www.thecodecage.com/forumz

0
joel
4/26/2010 4:25:01 PM
Hi Joel,

Thanks for your post and reference link. I ran the routine and it does place 
the names in column "A" with its adjacent values, however what I'm realyy 
needing to do is have the name listed once In Column "A" and any value having 
the same name association place it in the appropriate columns B, C, D & E 
next to the name.

So if you have any additional suggestion that would be great.

thanks again.
George

"joel" wrote:

> 
> This is very similar to a request I did on Sunday.  I modified the code
> from Sunday below.  Here is the link to Sundays request
> http://tinyurl.com/33sz3mj
> 
> 
> Sub LookupNames()
> 
> 'put names into column IV
> 'then use advancefilter to put names at bottom
> 'of worksheet
> 
> 'use data in column A to get Last Row
> LastRow = Range("A" & Rows.Count).End(xlUp).Row
> 'put final list 5 rows down from last date
> NewRow = LastRow + 5
> 
> 'put header in IV1 so advance filter doesn't create duplicate entry
> Range("IV1") = "Unique Names"
> 'copy first set of names in column B to column IV
> Range("A1:A" & LastRow).Copy _
> Destination:=Range("IV2")
> 'get last row of new data
> LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
> 'Copy Second List of names in column D to column IV
> Range("C1:C" & LastRow).Copy _
> Destination:=Range("IV" & (LastRowNewData + 1))
> 'get last row of new data
> LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
> 'Copy third List of names in column F to column IV
> Range("E1:E" & LastRow).Copy _
> Destination:=Range("IV" & (LastRowNewData + 1))
> 'get last row of new data
> LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
> Range("G1:G" & LastRow).Copy _
> Destination:=Range("IV" & (LastRowNewData + 1))
> 'get last row of new data
> LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
> 'use Advance filter to move copy data
> 'put Data 1 starting one row below NewRow
> Range("IV1:IV" & LastRowNewData).AdvancedFilter _
> Action:=xlFilterCopy, _
> CopyToRange:=Range("A" & (NewRow - 1)), _
> Unique:=True
> 
> 'delete temporary data in column IV
> Columns("IV").Delete
> 
> LastRowUnique = Range("A" & Rows.Count).End(xlUp).Row
> 'Unique names goes from NewRow to LastRowUnique
> '=IF(ISERROR(VLOOKUP(A10,A$1:A$4,2,False)),"",VLOOKUP(A10,A$1:A$4,2,False))
> '=IF(ISERROR(VLOOKUP(A10,C$1:C$4,2,False)),"",VLOOKUP(A10,C$1:C$4,2,False))
> '=IF(ISERROR(VLOOKUP(A10,E$1:E$4,2,False)),"",VLOOKUP(A10,E$1:E$4,2,False))
> '=IF(ISERROR(VLOOKUP(A10,G$1:G$4,2,False)),"",VLOOKUP(A10,G$1:G$4,2,False))
> 
> Lookup1Str = "VLookup(A" & NewRow & ",A$1:B$" & LastRow & ",2,False)"
> Lookup2Str = "VLookup(A" & NewRow & ",C$1:D$" & LastRow & ",2,False)"
> Lookup3Str = "VLookup(A" & NewRow & ",E$1:F$" & LastRow & ",2,False)"
> Lookup4Str = "VLookup(A" & NewRow & ",G$1:H$" & LastRow & ",2,False)"
> 
> Range("B" & NewRow).Formula = _
> "=IF(ISERROR(" & Lookup1Str & "),""""," & Lookup1Str & ")"
> Range("C" & NewRow).Formula = _
> "=IF(ISERROR(" & Lookup2Str & "),""""," & Lookup2Str & ")"
> Range("D" & NewRow).Formula = _
> "=IF(ISERROR(" & Lookup3Str & "),""""," & Lookup3Str & ")"
> Range("E" & NewRow).Formula = _
> "=IF(ISERROR(" & Lookup4Str & "),""""," & Lookup4Str & ")"
> 
> 
> 'copy formula down column B for each unique name
> Range("B" & NewRow & ":E" & NewRow).Copy _
> Destination:=Range("B" & NewRow & ":B" & LastRowUnique)
> 
> End Sub
> 
> 
> -- 
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198519
> 
> http://www.thecodecage.com/forumz
> 
> .
> 
0
Utf
4/26/2010 6:44:02 PM
Try adjusting the column widths.  The code places formulas in columns B,
C, D, & E.  Do you have the formulas?  Are you looking to put values
instead of formulas? Not sure why you aren't getting the correct
results.  The code works properly on my PC.

The code puts the results in Columns A four rows below the end of your
data.  Check to make sure there isn't anything else in column A below
your data.


-- 
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198519

http://www.thecodecage.com/forumz

0
joel
4/26/2010 7:39:48 PM
Hi Joel,

I really wanted just the values but the formula's are ok. I've  attached the 
results I'm getting which are close but not quite there yet. I don't want the 
headers "Col C", "Col E", & "Col G"  in the list of names in Col A.  
Additionally, I want the headers "Data 2', "Data 3" & "Data 4" at the top of 
their respective columns. Finally, the data that was used to create the list 
can be deleted.

Unique Names				
Col A	Data 1			
Name1	0.3	1	15	0.1
Name2	0.5	0.1	12	0.2
Name3	0.7	0.9		
Name4	0.7	0.9	18	0.3
Col c		Data2		
Col E			Data3	
Name5			18	0.3
Col G				Data 4


Many Thanks Again,
George

"joel" wrote:

> 
> Try adjusting the column widths.  The code places formulas in columns B,
> C, D, & E.  Do you have the formulas?  Are you looking to put values
> instead of formulas? Not sure why you aren't getting the correct
> results.  The code works properly on my PC.
> 
> The code puts the results in Columns A four rows below the end of your
> data.  Check to make sure there isn't anything else in column A below
> your data.
> 
> 
> -- 
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198519
> 
> http://www.thecodecage.com/forumz
> 
> .
> 
0
Utf
4/26/2010 9:33:01 PM
The changes you asked for were pretty simple to make.  i didn't test the
changes but I'm pretty confident they should work.  I used Pastespecial
to remove the formulas and then deleted the rows with the original data.
 I had to replace the values otherwise when I delete the orginal data
the formulas data would be lost.

Using formulas like this makes the macro run quicker than other methods.
 It looks like a lot of code, but if you look closely I just repeating
the same basic method over and over again. It only takes me about 10
minutes to write this macro.  It probably would take you hours.

I wasn't sure when I wrote the macro if you had a header row or didn't
have a header row.  To remove the headers I changed some of the copy
methods to start at row 2 instead of row 1.


Sub LookupNames()

'put names into column IV
'then use advancefilter to put names at bottom
'of worksheet

'use data in column A to get Last Row
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'put final list 5 rows down from last date
NewRow = LastRow + 5

'put header in IV1 so advance filter doesn't create duplicate entry
Range("IV1") = "Unique Names"
'copy first set of names in column B to column IV
Range("A2:A" & LastRow).Copy _
Destination:=Range("IV2")
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'Copy Second List of names in column D to column IV
Range("C2:C" & LastRow).Copy _
Destination:=Range("IV" & (LastRowNewData + 1))
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'Copy third List of names in column F to column IV
Range("E2:E" & LastRow).Copy _
Destination:=Range("IV" & (LastRowNewData + 1))
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
Range("G2:G" & LastRow).Copy _
Destination:=Range("IV" & (LastRowNewData + 1))
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'use Advance filter to move copy data
'put Data 1 starting one row below NewRow
Range("IV1:IV" & LastRowNewData).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("A" & (NewRow - 1)), _
Unique:=True

'Put Headers above row
Range("B" & (NewRow - 1)) = "Data 1"
Range("C" & (NewRow - 1)) = "Data 2"
Range("D" & (NewRow - 1)) = "Data 3"
Range("E" & (NewRow - 1)) = "Data 4"

'delete temporary data in column IV
Columns("IV").Delete

LastRowUnique = Range("A" & Rows.Count).End(xlUp).Row
'Unique names goes from NewRow to LastRowUnique
'=IF(ISERROR(VLOOKUP(A10,A$2:A$4,2,False)),"",VLOOKUP(A10,A$2:A$4,2,False))
'=IF(ISERROR(VLOOKUP(A10,C$2:C$4,2,False)),"",VLOOKUP(A10,C$2:C$4,2,False))
'=IF(ISERROR(VLOOKUP(A10,E$2:E$4,2,False)),"",VLOOKUP(A10,E$2:E$4,2,False))
'=IF(ISERROR(VLOOKUP(A10,G$2:G$4,2,False)),"",VLOOKUP(A10,G$2:G$4,2,False))

Lookup1Str = "VLookup(A" & NewRow & ",A$2:B$" & LastRow & ",2,False)"
Lookup2Str = "VLookup(A" & NewRow & ",C$2:D$" & LastRow & ",2,False)"
Lookup3Str = "VLookup(A" & NewRow & ",E$2:F$" & LastRow & ",2,False)"
Lookup4Str = "VLookup(A" & NewRow & ",G$2:H$" & LastRow & ",2,False)"

Range("B" & NewRow).Formula = _
"=IF(ISERROR(" & Lookup1Str & "),""""," & Lookup1Str & ")"
Range("C" & NewRow).Formula = _
"=IF(ISERROR(" & Lookup2Str & "),""""," & Lookup2Str & ")"
Range("D" & NewRow).Formula = _
"=IF(ISERROR(" & Lookup3Str & "),""""," & Lookup3Str & ")"
Range("E" & NewRow).Formula = _
"=IF(ISERROR(" & Lookup4Str & "),""""," & Lookup4Str & ")"

'copy formula down column B for each unique name
Range("B" & NewRow & ":E" & NewRow).Copy _
Destination:=Range("B" & NewRow & ":B" & LastRowUnique)

'replace formulas with values
Rows(NewRow & ":" & LastRowUnique).Copy
Rows(NewRow & ":" & LastRowUnique).PasteSpecial _
Paste:=xlPasteValues

'delete orignal data
Rows("1:" & (NewRow - 2)).Delete
End Sub


-- 
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198519

http://www.thecodecage.com/forumz

0
joel
4/26/2010 10:02:16 PM
Joel,

Sorry for the delay, This worked perfect...Thanks again.

"joel" wrote:

> 
> The changes you asked for were pretty simple to make.  i didn't test the
> changes but I'm pretty confident they should work.  I used Pastespecial
> to remove the formulas and then deleted the rows with the original data.
>  I had to replace the values otherwise when I delete the orginal data
> the formulas data would be lost.
> 
> Using formulas like this makes the macro run quicker than other methods.
>  It looks like a lot of code, but if you look closely I just repeating
> the same basic method over and over again. It only takes me about 10
> minutes to write this macro.  It probably would take you hours.
> 
> I wasn't sure when I wrote the macro if you had a header row or didn't
> have a header row.  To remove the headers I changed some of the copy
> methods to start at row 2 instead of row 1.
> 
> 
> Sub LookupNames()
> 
> 'put names into column IV
> 'then use advancefilter to put names at bottom
> 'of worksheet
> 
> 'use data in column A to get Last Row
> LastRow = Range("A" & Rows.Count).End(xlUp).Row
> 'put final list 5 rows down from last date
> NewRow = LastRow + 5
> 
> 'put header in IV1 so advance filter doesn't create duplicate entry
> Range("IV1") = "Unique Names"
> 'copy first set of names in column B to column IV
> Range("A2:A" & LastRow).Copy _
> Destination:=Range("IV2")
> 'get last row of new data
> LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
> 'Copy Second List of names in column D to column IV
> Range("C2:C" & LastRow).Copy _
> Destination:=Range("IV" & (LastRowNewData + 1))
> 'get last row of new data
> LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
> 'Copy third List of names in column F to column IV
> Range("E2:E" & LastRow).Copy _
> Destination:=Range("IV" & (LastRowNewData + 1))
> 'get last row of new data
> LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
> Range("G2:G" & LastRow).Copy _
> Destination:=Range("IV" & (LastRowNewData + 1))
> 'get last row of new data
> LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
> 'use Advance filter to move copy data
> 'put Data 1 starting one row below NewRow
> Range("IV1:IV" & LastRowNewData).AdvancedFilter _
> Action:=xlFilterCopy, _
> CopyToRange:=Range("A" & (NewRow - 1)), _
> Unique:=True
> 
> 'Put Headers above row
> Range("B" & (NewRow - 1)) = "Data 1"
> Range("C" & (NewRow - 1)) = "Data 2"
> Range("D" & (NewRow - 1)) = "Data 3"
> Range("E" & (NewRow - 1)) = "Data 4"
> 
> 'delete temporary data in column IV
> Columns("IV").Delete
> 
> LastRowUnique = Range("A" & Rows.Count).End(xlUp).Row
> 'Unique names goes from NewRow to LastRowUnique
> '=IF(ISERROR(VLOOKUP(A10,A$2:A$4,2,False)),"",VLOOKUP(A10,A$2:A$4,2,False))
> '=IF(ISERROR(VLOOKUP(A10,C$2:C$4,2,False)),"",VLOOKUP(A10,C$2:C$4,2,False))
> '=IF(ISERROR(VLOOKUP(A10,E$2:E$4,2,False)),"",VLOOKUP(A10,E$2:E$4,2,False))
> '=IF(ISERROR(VLOOKUP(A10,G$2:G$4,2,False)),"",VLOOKUP(A10,G$2:G$4,2,False))
> 
> Lookup1Str = "VLookup(A" & NewRow & ",A$2:B$" & LastRow & ",2,False)"
> Lookup2Str = "VLookup(A" & NewRow & ",C$2:D$" & LastRow & ",2,False)"
> Lookup3Str = "VLookup(A" & NewRow & ",E$2:F$" & LastRow & ",2,False)"
> Lookup4Str = "VLookup(A" & NewRow & ",G$2:H$" & LastRow & ",2,False)"
> 
> Range("B" & NewRow).Formula = _
> "=IF(ISERROR(" & Lookup1Str & "),""""," & Lookup1Str & ")"
> Range("C" & NewRow).Formula = _
> "=IF(ISERROR(" & Lookup2Str & "),""""," & Lookup2Str & ")"
> Range("D" & NewRow).Formula = _
> "=IF(ISERROR(" & Lookup3Str & "),""""," & Lookup3Str & ")"
> Range("E" & NewRow).Formula = _
> "=IF(ISERROR(" & Lookup4Str & "),""""," & Lookup4Str & ")"
> 
> 'copy formula down column B for each unique name
> Range("B" & NewRow & ":E" & NewRow).Copy _
> Destination:=Range("B" & NewRow & ":B" & LastRowUnique)
> 
> 'replace formulas with values
> Rows(NewRow & ":" & LastRowUnique).Copy
> Rows(NewRow & ":" & LastRowUnique).PasteSpecial _
> Paste:=xlPasteValues
> 
> 'delete orignal data
> Rows("1:" & (NewRow - 2)).Delete
> End Sub
> 
> 
> -- 
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198519
> 
> http://www.thecodecage.com/forumz
> 
> .
> 
0
Utf
4/30/2010 3:47:02 PM
Reply:

Similar Artilces:

How to create a partition on an external USB drive?
Toshiba Tablet XP Home/Student with plenty of memory. I have the program DriveImage XML. At one point it says to create a partition somewhere for the image it's going to create. I have a 40G external plug and play HD. I'd like to create a partition on it without disturbing other files and folders I have on it. There's plenty of free space. Various resources say use XP Disk Management to create that partition. So when I go there it appears as they say but when they say click on the free space choose the selection "Create new partiton." I have right clicked ...

Lowest entry in a column
Hi everyone, Can anyone tell me how to automatically use the last/lowest entry in a column? I don't want to sort the cells, or choose the Maximum or Minimum - I just need to use the bottom entry in a column automatically in a formula I'll create somewhere else on the spreadsheet. It thought it would be in the functions list somewhere, but it has eluded me! Thanks, Astley Suppose A is the column in question, use the following formula to refer to the last cell: =INDIRECT("A"&COUNT(A:A)) Mangesh "Astley" <ast@exemail.com.au> wrote in message ne...

Auto fill and drop down lists
The fields are Rank and Rank Sort (I cant do this alpabetically as a the ranks (Military) don't run in alphabetical order. The idea is this: The form (Pers Details Form) contains personal details Name, Number etc from the Pers Details Table. There will be box where I can select the rank from a dropdown list. The Rank details are contained in a seperate table (Rank Table) containing a Rank and Rank Sort column. In the Pers Details Form I can create a record, enter details but I want to have a drop down box to choose the rank from. Once chosen I would like the rank to show (but ...

user created shapes non printing
I started have a problem with vision 2002 that I have not noticed before. When I create a new shape, by default, it assumes the non-printing properly under FORMAT � BEHAVIOR. Also if I group a set of "printing" shapes the group will become non-printing. Can I change this behavior? How are you creating the new shape? Also are you using layers in your document? -- Mark Nelson Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "Robert" <hammer_757@hotmail.com> wrote in message news:9ec427f7.0409231005.576...

Price list 02-23-06
Does anyone know how to default a quote to a particular price list, or can I remove this mandatory field? Tks. Ian Hi Ian, You could write a script at the OnLoad Event of the Quote Form. var CRM_FORM_TYPE_CREATE = 1; var CRM_FORM_TYPE_UPDATE = 2; switch (crmForm.FormType) { case CRM_FORM_TYPE_CREATE: alert("This is a create form."); //insert the PriceList GUID into the Price List look up field here. break; case CRM_FORM_TYPE_UPDATE: break; } Hope this helps. -- Manisha Powar (Madhusudanan) Program Manager Microsoft Dynamics - CRM This post...

Module name in
Hi, when your are in the EXCEL code editor, you have on your left the name of the modules. I find anoying the fact of having to remember the module name or edit one by one each modules to find for intense a piece of code as a reference. Is there a better way to do it in that environment like renaming the module with specific name? Thank's ahead!. You can rename the module. Most of the people here probably group their macros into similar function macros in different modules, and name the module accordingly. This makes it much simpler to find. You can also do a find, Ctrl-F, and set the ...

Machine name
Hi You can get all sort of information in INFO function. But, How can i get the local machine name ? avner :( --- Message posted from http://www.ExcelForum.com/ || Hi || You can get all sort of information in INFO function. || But, || How can i get the local machine name ? || avner || :( || || || --- || Message posted from http://www.ExcelForum.com/ Start-settings-control panel-network-identification Avner, You need to use the GetComputerName API call in a VBA function. For example, Declare Function GetComputerName Lib "kernel32" _ Alias "GetComputerNameA" ...

"All users" "Programs" create/modify shortcut from app...
Hi all, I've created two shortcuts into "Programs" folder for "All Users" It lets me to get them available for all user. The problem: Application running in "User" context needs to delete and re-create such links but it fails due to an "access denied" ... Settings correct permission to such links it starts working as well I've created links using the IShellLink/IPersistFile sehll interfaces. So, I actually need to have link under "programs" for "All Users" which might be modified by application running in "Users"...

Names, Addresses In A Table...
I Am Putting Names And Addresses In A Table For A Directory I Am trying To Put Together, 6 Rows And 3 Columns. 18 Companies To A Page, I Will Have Approx. 300 Pages When Complete. My Question Is: Is There Any Way To Make Them Go In Alphabetical order By Name Of Company? I Am Very Greatful To Anyone Who May Be Able To Give Me Some Help. Thank You very Very Much. PS: I Am New To This Kind Of Software And Would Like To Say To MS PUBLISHER...You Are Very RUDE!! This is a multi-part message in MIME format. ------=_NextPart_000_01E6_01C43075.4641A2B0 Content-Type: text/plain; charset="Utf...

stacked column with total
I created a stacked column chart with 2 series. I'd like to show the total value on top each bar. Right now, show value displays each value of independently. For example, I have a bar showing 3 and 2 stacked but I would like to show 5 (3+2) on the top bar. I've seen on someone graph before. I can't recreate it. Pls help. Thanks Mat Mat Check here http://andypope.info/charts/StackColTotal.htm -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk "matelot" <matelot@discussions.microsoft.com> ...

Preventing Recipient Names from Showing
I have a long list of people I am addressing a message to. I want the recipient to see that it is coming to them, but not be able to see anyone else's name/address that it's being sent to. I've tried the BCC field but then even the recipient does not see that it was sent to them. I'm afraid they will think it is junk mail if they don't see it addresses to themselves. Check out http://www.slipstick.com/addins/mail.htm#massmail for other options -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked http://www.slipstick...

How to create a connection point in Excel
When I group autoshapes the group itself does not have connection points. A connector connects to one of the grouped shapes instead. So, the connector beginconnecedshape (or endconnectedshape) property contains the name of the contained shape and not the name of the group. Is there a way to create connection points for a group? Alternatively, is it possible to change a group into a single shape with connection points? ...

referencing to worksheet names in macro for each new worksheet inserted
Hi I created a code to insert new worksheets and rename them according t values on the new worksheet itself. Say in Cell D1, i have th worksheet name. My question is when i want to refer to this worksheet in subsequen coding, how should i code it? For eg, How should i write the ???? for Sheets("????").select? Would creatin the a variable to store the names help? Thanks in advance Ken -- Message posted from http://www.ExcelForum.com After inserting your new worksheet set it's name equal to a variable. For example SHEETS.ADD VWORKSHEET = ACTIVESHEET.NAME This method ...

outlook 2007 monthly calendar six column?
Just converted to Outlook 2007 from 2003, where I could print a monthly calendar with 6 columns: Mon Tue Wed Th Fri Sat/Sun. This freed up some width per column, b/c the weekend days were consolidated. Can't seem to do this with '07. The columns are too skinny (even on landscape) and I can't read appts. Advice? Thx Try the calendar printing assistant or word template- see http://slipstick.me/calprint for links. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ ...

Help with automating file name
I have the following code that exports the below query to excell. I would like the files name to include the month and date. How would I format this? DoCmd.OutputTo acOutputQuery, "qryShopOrderSqFtShippedSummaryExport",_ acFormatXLS, "W:\Cokato\Production\ProdRoomRpt.xls" -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1 Hi, Matt. > I would > like the files name to include the month and date. Uh, . . . the date _always_ includes the month, unless you're r...

Combo Columns
I've created a combo box on a form in Access using 2 columns. The first column is hidden so the second column is the only one displayed in the combo box. When I then use that combo as the source in a separate text box the answered returned is the first column. Any idea how I get the second column information instead? -- Cheers. Paul ...

Createing Quote sometimes make Error: 80070057
Hello NG, Today I found some funny problems. I create Quotes via the quote WebService from the CRM. This normally works fine, but now I have some quotes, which will get me an error, when I open it. This error is no normal error, I will get an white/yellow Page with ASPX Errorpage: Here the Message (sorry but I only have this error in German): Serverfehler in der Anwendung '/'. ---------------------------------------------------------------------------- ---- Falscher Parameter. Beschreibung: Beim Ausf�hren der aktuellen Webanforderung ist ein unverarbeiteter Fehler aufgetret...

Combine 2 rows if name is same in Column B & C on both
Combine 2 rows if name is same in Column B & C on both 2 spreadsheets - Sheet 1 is bigger with extra names in column B & C Lastname Firstname Both - Column B & C Lastname Firstname - both sheets Sheet 1 has data in Col. D & E Sheet 2 has data in Col. F & G Sheet 1 has extra names not in Sheet 2 If Sheet 1 B&C = Sheet 2 B&C , then add F&G columns from sheet 2 , behind D& E columns on sheet 1 , for the match of names in Column B & C Thanks kerns.walter@epa.gov On Nov 13, 12:50 pm, wk <kerns.wal...@epa.gov> wrote: > Combine 2 rows if name...

List box with available queries question
I have a list box control on a form and want the list box to display all the queries within the database. The following code is what I have so far but it does not work. Any help is appreciated. SELECT [Name] FROM MSysObjects WHERE [TYPE] = 5 and LEFT([Name],1) <> "-" ORDER BY [Name]; "Billy B" <BillyB@discussions.microsoft.com> wrote in message news:F47BB77B-7B66-4860-8954-F4FE32FE7C3C@microsoft.com... >I have a list box control on a form and want the list box to display all >the > queries within the database. The following code is what...

Force command to run for each computer in txt file list of compute
I have a few hundred computers that haven't been defragged in years. I was wondering if rather than doing it manually, if someone had a script where if i just put all the computer names in a txt file, it would iterate through the list and perform a "defrag c: -f" on them all from my workstation. The workstations all have the same local administrator password, so the process would be the same for each. I just dont know how to write scripts. I know if i were going to do this manually from my computer I would run the command below for each ComputerName psexec ...

Creating Control Grid
Hi all, I would like to know as to how I should go about creating a control grid of my very own. I just need a bit of push (suggestions). Thanks In Advance Where are you getting stuck? Of course, if the grid isn't too large and most of the cells will have data, then you could simply use a two-dimensional array. For larger grids that will be sparsley populated, there are a couple of algorithms to consider. A simple one is to create a one-dimensional array that represents the rows (or columns), and have each item contain or reference a linked list for all items in that row (complete...

Filtering a column to exclude any repeated entries.
Hi, I am working on a column that has the same entries 2 or more times. Is there a way (copy-paste or other) to get only unique entries from this column (that is to exclude repeated entries)? Any help apprieciated, Thank you. If you go to Data/Filters/Advanced Filters there is a box at the bottom left that you can tick to return unique entries only. >-----Original Message----- >Hi, > >I am working on a column that has the same entries 2 or more times. Is there >a way (copy-paste or other) to get only unique entries from this >column (that is to exclude repeated...

SBS VPN Oddity with Name Resolution
We host a number of intranet sites for employee use (not hosted on the SBS box itself - they're on member servers). Some are published, most are not. I've trained our laptop-using employees to use the "Connect to Small Business Server" VPN connection to reach our intranet-only websites when offsite. I haven't seen issues with users reaching the Internet-facing sites even when users switch back and forth between connecting and disconnecting the SBS VPN during a session. My boss recently informed me that he couldn't reach one of our published sites fr...

eConnect or Web Servies to create RMTransaciton?
Hello, I am new to GP 9.0 development using web services and eConnect. Can someone help me decide which technique I can use to do the following: In GP 9.0, if I go to: Transactions-->Sales-->Transaction Entry brings up the Receivables Transaction Entry screen, where I can fill in a number, batchid, customerid, and some sales figures, etc. I am using .NET and can use the eConnect API to create a taRMTransaction to submit using eConnect. And it works fine, the transaction creates in GP. Can I perform the same thing with any of the web methods available via the GP 9 web services? I ca...

compare two columns with different ranges in two worksheets
I need to compare two columns of data in two different worksheets and display a third one. Here it is an example: -(worksheet1!A1:A10), (worksheet1!B1:B10) and (whorksheet2!C1:C25) -this is my query, if C5 is already in (A1:A10) I want to display B5 in worksheet2!D5 I think it is tricky because you need to identity which row in the A1:A10 is equal to C5 to display B5 and the range are different. you could save my day chris90 In worksheet2!D1: =if(isna(vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)), "", vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)) HTH Kostis Vezerides brilliant, ma...