Excel 2010 :: Code To Extract Unique List Of Dependents?
May 31, 2012
Excel 2010ABCDEFGH1EMPLeaderSamAsbertNoellaJackson2RosalineSam3LionelAsbert4KerryNoella5JohnnyNoella
6AliAsbert7RosalineSam8TimothyAsbert9TimothySam10ReginaldAsbert11PascualJackson12MichaelSam13ReginaldJackson
14MeganJackson15ShellySam16CandiceSam171819Sheet1
Here's my problem... Column A contains employee name & column B contains Team Leader name of the respective employee. Range D1:G1 should contain names of team leaders from column B. After that, depending on the name populated in D1, cells going downwards from cell D2 should contain name of employees of that team leader. Similar thing should be repeated for columns E, F, & G. If an employee shows up under two team leaders then it should be displayed under both lists.
Is there any formula/VBA code which can do this? Been after this for last 3-4 hours and now just lost in a maze of various Index Match combinations and array formulas..
View 9 Replies
ADVERTISEMENT
Dec 6, 2012
I have previously used the following code to successfully pull out IE webpage source code for string manipulation.
Its a crude example to demonstrate the principle:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public IE As Object
Sub Sample()
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
[Code] ......
However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"
The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......
The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.
Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?
Using :
MS Excel 2010
IE Explorer 8.0
View 1 Replies
View Related
Oct 30, 2013
I have lots of DVDs and decided to catalogue them using Excel 2010. I made use of 3 Sheets. In Sheet 3 there are 2 lists: Category (PG, M18, General, Adult) and Language (English, Chinese, Japanese, Korean, Spanish). Sheet 2 contains the data under the following headings: Title, Category, Language, Genre. I used Sheet 1 to create a drop down list for selecting a Title and then the information of the title are displayed, that is, its category, language and genre (I used vlookup). This is working fine.
Then I created another drop down list for Category. I want to be able to select a category, such as M18, and then a list of titles that fall under this category will be displayed. However, when I tried using vlookup with index, I failed to get the display of titles.
View 1 Replies
View Related
Mar 3, 2013
I'm trying to extract a list of names from an Excel 2010 table based on two criteria, thus:
=IFERROR(INDEX(Database[FullName],AGGREGATE(15,6,ROWS(Database[Age]-ROW('Database'!$A$2))/((Database[Age]>1.8)*(Database[Age]
View 4 Replies
View Related
Jan 21, 2014
Excel 2010. There is a limit to the usability of the
=INDEX($A$1:$A$1000,MATCH(0,COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0))
method. It seems like the limitation is in the Countif function going over 1000 (or some other size limit)
I have a list of ~1500 rows, of that there are approximately 55 unique items. Doing the unique array works correctly up until item ~40, upon which it fails by returning the 1st item only (for the rest).
Trying to debug, and pulling out the Match section, it functions up until 976 (that is
MATCH(0,COUNTIF($N$1:$N40,$C$3:$C$1500),0)
returns 976) anything after returns just 1 (1st item).
It seems to be a limitation on the text string size that COUNTIF can handle.
View 7 Replies
View Related
Sep 15, 2013
I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)
The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.
Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b
My system is Windows 8 Excel 2010.
View 7 Replies
View Related
Jul 13, 2009
I have a two worksheets of workbook named LC worksheet and second one Account work sheet.
Data from LC worksheet likned into the Account worksheet at Column K and Column V.
I want to highlight cells in LC worksheet which have no dependents or have duplicate dependents at Column K and Column V into the Account worksheet. Built in trace dependents is time consuming and useless.
File attached for your further understanding
View 11 Replies
View Related
Jul 18, 2007
I am trying to write some code to run through a workbook and identify dead ends i.e. cells with no dependents. I can isolate the cells I need to check and tried to count dependent cells to see which had none. My code to do this is:
If Cell.Dependents.Count < 1 Then
This creates "runtime error 1004: no cells found" when a cell with no dependents is found. I have tried using ISERROR and ISNUMBER and a few other things to either trap this error or turn it into something useful. It seems that whatever is returned from my expression is not an error code or a null or a number.
View 3 Replies
View Related
Mar 13, 2008
I'm disecting someone's spreadsheet to automate it.
I'm looking for some code that will make a list or table of cells with dependents (not on the active sheet) and show what the dependent is. I'm assuming the code would list all values or formulas on the active sheet that get used somewhere 'NOT' on the active sheet.
My results would be basically 2 columns. Column 1 would be a list of tab name & cell refrences from the active sheet when the macro was run and column 2 would be the tab name & cell reference of where the data goes within this book.
View 3 Replies
View Related
Jun 17, 2008
I would like to trace dependents of all cells <> "" in column A of a sheet "Tickmarks". If there is a cell in column A which has a value, but does not have a dependent I would like that value to be stored. When the trace dependents check is done for all the cells with values in column A, I would like to display a msgbox with all the cell values collected above.
Eventually, I will be trying to locate the cells with no dependents but with values in Column A and replace them with cells with dependents and change the dependent links to the updated cells.
I have attached an example.
Sub Trace_Dependents_Tickmark_Sheet()
Dim RowCounter As Integer
RowCounter = 3
'Select firsct cell with t/m & show dependents
Range("A" & RowCounter).ShowDependents
' Loop until RowCounter = 200
Do
If Range("A" & RowCounter).Value = 0 Then
RowCounter = RowCounter + 1
View 7 Replies
View Related
May 30, 2014
I have two lists in different columns, which are defined ranges. I want to extract them to a unique list with an array formula and at the same time sort albafetically, without duplicates, like this:
List1
List2
Final List
Audi
Ford
[Code] ....
So far, I have this formula which is working in what concerns removing duplicates and exctract a unique list. But it is not ordering alfabeticaly.
{=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), "")}
View 2 Replies
View Related
Jul 4, 2014
I'm creating a database that contains a columns of hyperlinks and background color.
I've created code on Excel 2010 with advanced filters to copy selected rows to another worksheet. The extract works fine and those cells with hyperlinks and various color codes from the original database "appear" to have been extracted correctly.
However, a closer look shows the hyperlinks on the target worksheet are just blue underlined texts...with the links no longer working. Is there a way for me to correct this?
View 9 Replies
View Related
Jun 20, 2014
I have data set up like the example but then for 1797 lines.
D39BKYes
D39BKYes
D39BKYes
D39BKYes
GHFLBNo
R80FANo
R80FANo
[code].....
What I need to get in a pivot table is: two columns or rows (Yes / No) and the unique count of the code.In this case is should show Yes: 2 and No: 3
View 2 Replies
View Related
Jul 9, 2013
I have a wookbook with serial numbers in column A and barcodes in column B. There are 51940 rows in total.
Is there a way I can search through column A, and for each unique value found , concatenate all of the barcodes that relate to that serial number in column C?
Serial Numbers | Barcodes | Combined Values
-------------------------------------------------------
abc123 abc12300 abc12300,abc12301, etc etc
abc123 abc12301
def456 def45600 def45600, def45601, etc etc
ghi789 def45600
i am using Excel 2010.
View 3 Replies
View Related
Jun 21, 2013
How do count unique values in category in pivot table. (my table, im taking data from ms query). I am using excel 2010
Pivot table example: the result i want is the "no of types" as my data only show "category" and "types".
Category
Types
No of Types
Fruits
Apple
Pear
Orange
3
Vegetable
Cabbage
1
View 4 Replies
View Related
Jul 31, 2014
I have 3 ranges of data which each have a quantity and a length. I want to create from these ranges a list of unique values with the total quantity required of each value (as shown). Inputs on any column may be blank but where there is a length, there will be an adjacent quantity to the left.
I'm using Excel 2010.
View 9 Replies
View Related
Aug 29, 2012
I am using the following code to copy a unique list, but it gives a duplicate in the first 2 rows(col Q). There is no headings in the columns.
Code:
Sub CopyUnique()
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "P").End(xlUp).row
ActiveSheet.Range("P11:P" & lastrow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("Q11"), Unique:=True
End Sub
View 4 Replies
View Related
May 19, 2014
I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.
View 5 Replies
View Related
Mar 8, 2012
I'm using Excel 2010 on a Windows 7 machine.
I have one sheet with a large list of dates and total prices.
I have a second sheet with a list of dates and unit prices.
I want to divide the total price in my first list by the unit price on my second list.
I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.
My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.
View 2 Replies
View Related
Jul 18, 2012
How do I create a 6 digit unique random number for use as an ID in column A. Once created the rows with preexisting 6 digit unique random ID numbers must not change every time new rows are added.
View 9 Replies
View Related
Feb 4, 2013
I'm trying to compile a VBA that would allow me to compare 2 columns "A" in different worksheets (same Workbook) and output any unique values to 3rd worksheet together with the rest of the values in the corresponding row.
Sheet1
A
B
C
[Code]....
Excel 2010
View 9 Replies
View Related
May 5, 2014
[URL]
You can download the excel from the above link.
This excel includes some text data, in each cell there is an email address along with additional text. I only want to get the email addresses from the entire data.
View 5 Replies
View Related
May 25, 2014
I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.
Col A
ABCD
ABCD
ABCE
BDCE
BDCF
ZXYE
ZXYE
YEZY
CDEF
Col B is where i would like to list all the unquie vaules with a count e.g.
ABCD 2
ABCE 1
BDCE 1
BDCF 1
ZXYE 2
YEZY 1
CDEF 1
View 14 Replies
View Related
May 22, 2012
Using excel 2007. I have a column with multiple items, a lot repeated.....how do I make a to show just one of each item? I want a unique list of my column of repeated items.
View 3 Replies
View Related
Mar 28, 2014
I have a folder with multiples excel sheets
Destination : C: Project CustomerExcel
I would like to extract and compile the information contained in these cells:
a5,c5,a6,c6,c7,a14,g14,e16,g16,e18,i18,a20,g20,h22,j22,h24,l24 all the sheets.
New sheet would contain the information of each sheet eg. Column A2= file name
And Row B2 to R2 or whatever will be the corresponding cells mentioned above for each cell. The code I have only brings back the file name but only a5 from the range but not the rest.
VB:
Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
[Code] .....
View 5 Replies
View Related
Mar 27, 2014
I am using Excel 2010. I need to copy a list from any Column on the right to the existing Column. See the sample file.
View 10 Replies
View Related
Apr 24, 2014
I have windows 7 and excel 2010 and am using a macro that opens up a csv file(I think) of daily reports into Excel and then automatically delineates it and formats it how I want it. I will be using this to save a new file every day for the reports from the previous day and want to include at the end of that macro a way to prompt the user to "save as" so that each day they can run the macro and enter in the date and save that report for further use. I am wondering what VBA code I could use at the end of the macro code to prompt the Save As box and if I could already have the save us set up in the following folder... "W:Daily to Fortis Excel2014(the user will put in the date here)".
I've been looking around sites and trying to figure it out. I need the file format to be the same as when you save as "Excel Workbook". I was trying to use the Saveas (filename) function and could get it to save every time as a specific file name in that location but when I run it the next day it has the same name and saves over itself. So I need the user to be able to put in todays date as the filename to create a new one every day.
View 7 Replies
View Related
Jun 2, 2014
The only change I made was to change the "Sheet1" to "Journal" to match the worksheet name in my workbook.
As you close and reopen the workbook, the timer should start with =NOW() in A1 (formatted as "HH:MM:SS") and count up with the current time until you close the workbook.
I use Excel 2010, could that be the problem, that I copied a VBA code for an older version of Excel???
Question: What exactly is a regular module, do I use Module 1 for the first portion of the code or place it in ThisWorkbook?
AUTO TIME UPDATE VBA
PLACE IN REGULAR MODULE
[Code] ....
View 7 Replies
View Related
Jun 15, 2012
I have an excel 2010 spreadsheet that lists all of our vendors and the amount we spent with them over the last year.
I want to know how many of them were local vendors.
I have a list of all the zip codes within a 100 mile radius (there are about 500 zip codes). I would like to write a macro or sort function that searches the entire vendor list and only reports back those vendors that are included in the zip codes I specify.
The columns are as follows:
A B C D E
Vendor City StateZip CodeTotal AP
Vendor 1TROY AL36082527.37
Vendor 2PHOENIXAZ85054100
Vendor 3TUCSONAZ8571416255
I want to keep the all the columns, I just want to eliminate all of the vendors that do not fall within the zip code criteria I set.
View 5 Replies
View Related
Jul 5, 2012
I'm using Excel 2010 and XP. I have data in rows 1 -4 that can not be altered. There are headings in Row 5, B-L & Row 6, F-J.
Data starts in Row 7 and follows the headings. This repeats for several thousand rows. I need a way to move Row 6, F-J and put it on Row 5, M-Q
Row 6, F-J and put it on Row 5, M-Q
Row 8, F-J and put it on Row 7, M-Q
Row 10, F-J and put it on Row 9, M-Q
Row 12, F-J and put it on Row 11, M-Q
....
....
....
Then delete blank rows below Row 5
View 7 Replies
View Related
