List Uniques From Multiple Columns
May 4, 2007I would like to get a unique list of letters from multiple columns.
I tried to use this formula below but it does not work with multiple columns....
ADVERTISEMENT
List Uniques In Alphabetical Order
Nov 18, 2006I 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:...
Filter List To Only Show Uniques
Feb 20, 2008I have a list with many duplicate names in it, from which I only wanted 1 instance of each name, to be placed in another sheet. So, I recorded a macro that selects, from the sheet called "Data", column E (the column with the relevant list), copy/paste's it into sheet 3 in column A, selects the new column, and runs an Advanced Filter command on it, to identify and display all the unique names in it in the same place. It ran just fine when I recorded it. But then, when I deleted the data created by the macro and tried to re-run it, I got random data. I found that whenever I click on a different cell, it provides a different output. I only want it to select Column E, and copy paste that, not other random columns depending on which cell is currently selected. This is my
Sub Fetch_Unique_Names()
Sheets("Data").Select
ActiveCell.Range("A1").Select
ActiveCell.Range("E1:E2200").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Range("A1:A2176").AdvancedFilter Action:=xlFilterInPlace, Unique _
:=True
End Sub
How To Combine Multiple Columns Into Long List Of Results In 2 Final Columns
Oct 15, 2013I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...
Current layout with varying number of data points per row...
ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5
Desired Result
ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2
and so on and on and on until all rows have been filled in down the file...
Is this possible?
List Values From Multiple Columns In A Dropdown List
Dec 17, 2012how to list values from multiple columns in a dropdown list based on lookup value of 1st column as below.
This is how the table looks like.
Product MOLD1 MOLD2 MOLD3
4" AB1 AB2 AB3
6" ZA2 zd4 -
This is how the dropdown list should look like for Product 4"
ab1
ab2
ab3
Dropdown List From Multiple Columns
May 28, 2013I am trying to make a single drop-down list which contains values from two columns.
I should give you the example immediately:
NAME | SURNAME (these are two columns)
Jon | Alfa
Karl | Beta
Lilly | Wolf
And the drop-down list should look like this:
DROP-DOWN
Jon Alfa
Karl Beta
Lilly Wolf
I have already tried to solve my problem with data validation but with no success.
List Box With Multiple Columns And Headings
Jan 19, 2009I am trying to figure this out. I have a userform with one listbox with 7 columns that displays a range. It is working except i cannot get the column headings to work.
View 4 Replies View RelatedDrop Down List For Multiple Columns
Jan 5, 2008I have the following in a Validation/Data the following tries to reference and array f2:j11
=if(d5="",teammember,index(teammember,match(f2,teammember,0)))
The array 'teammember' refers to cells f2:j11. Formula returns an error message that it can only refer to a single row or column. Is there a formula
Single List To Multiple Columns
Aug 17, 2006I have a long list of 2 columns containing data as follows:
country1 date1-1
country1 date1-2
country1 date1-3
country2 date2-1
country2 date2-2
country3 date3-1
country4 date4-1
country4 date4-2
country4 date4-3
which I would need to move to get one column per country with the top cell the country name and below each name all the related dates, e.g.
country1 country2 country3 country4
date1-1 date2-1 date3-1 date4-1 etc.
date2-1 date2-2
date3-1
I have searched and found almost similar questions and tried to apply it to this case, but not being versed in VBA it was a failure on my part
Compile List From Multiple Columns / Rows
Jan 17, 2014I want to essentially create a meal plan and then have my shopping list generated from the ingredients columns/areas into a separate sheet for printing. I need shopping list generation. I have attached a sample of what I am currently working with but still needs a lot of tweaking. I am not the most advanced excel user as you can see but I continually try to improve upon all of my work.
Test_Meal_Plan.xlsx
Sorting A List Of Numbers By Multiple Columns
Oct 13, 2008I want to sort the list like this:
1) If there is a zero (null) value in all 3 months, these records should be at the bottom sorted by record name (I did not show this field in my file).
2) If there is a non-zero (non-null) value in any of the 3 months, the records will be sorted with each other by total change.
Is there a way to do this without me doing sorts multiple times and manually moving rows of data around (which is what I have done to arrive at the list I have attached)? I am not experienced with VBA or Macros, and would prefer a detailed explanation if a solution is using either method.
Adding Multiple Columns For Dropdown List
Mar 5, 2013I'm trying to set up a billing form for my work. I'm trying to include drop down list for each sections (personnel, equipment, and supplies), then all sum together as a bill. I'm not sure if this is doable in excel.
View 9 Replies View RelatedConvert Address List To Multiple Columns
Apr 10, 2009I'm looking for a way to convert an old church address list that is formatted in a single column. There are no blank rows, but the amount of rows for each entry can vary. For instance, there could be as little as 3 rows of data (church name, address, city/state/zip) or more if email addresses and websites are provided.
The data is currently in the format below (notice how one entry has a website while the other does not).
First Church
102 Main Street
Dallas, TX 12345
email@whatever.com
Second Church
500 Second Street
Austin, TX 12376
email2@whatever.com
http://www.boguswebsite.com
So I'm looking for the data to be formatted like the following:
First Church 102 Main Street Dallas, TX 12345 email@whatever.com
Second Church 500 Second Street Austin, TX 12376 email2@whatever.com http://www.boguswebsite.com
I was able to find the following code from a Google search, but it can't dynamically adjust the range.
Sub x()
Dim rng As Range
Set rng = Range("A1").Resize(5)
Do Until IsEmpty(rng.Cells(1, 1))
rng.Copy
Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial Transpose:=True
Set rng = rng.Offset(5)
Loop
End Sub
I think what I need to make this code work is a way to dynamically adjust the range so that it can determine when to move to the next row of data. Static ranges break the process due to the amount of data being provided not being uniform. What I was thinking is that I could use the word "church" as a start point and end point in a loop so the script knows when to jump to the next row and begin copying the proper number of columns. I'm just not sure how to accomplish this in vba.
Hiding Columns With Multiple List Boxes
Oct 1, 2006I have a user form with 4 list boxes that I'd each like to populate with their own lists of cities according to time zone - one list box for each time zone (ET, CT, MT and PT).
I attached a worksheet that has some code from another project I got from another thread on this forum, but it needs to be modified (or whole new code written) for this application, including initializing the control buttons.
The code I'm borrowing has a slight glitch in it, where if you select the last item in the list box to hide, that item will be missing from the list the next time the user form is opened, and you'll then have to manually unhide that column.
Pivot Table List From Multiple Columns
May 12, 2007I am trying to round a number to the next half penny. The mround function would seem to work but does not work for fractions.
View 2 Replies View RelatedList Unique Values Of Multiple Columns
Mar 12, 2008I need a UDF to make live easier. I have a huge excel file and I need a final function (I suppose a UDF) that lists all values from a range spanning over multiple columns and rows. I would not really like a macro, since it does not update when certain values are changed.
It's part of a bigger functionality, but I need this one, really bad, since it spans over 6000 rows as the search range, and 7000 as the search values. In short:
I got to list all the unique values. They are in specific cells on worksheet 1 (A1:C5 and A25:C31) and should be listed on worksheet2 starting in field A2. As 'simple' as that. Nothing more, nothing less, just list all unique values of a certain range, which I specify. If it can not be done, by selecting the two fields separately, it's no problem at all to list all values in eg A1:C6000. Do note that some of the cells will be blank, and I don't need blanks. I only need the actual data sets. I added a sample excel file, that shows a representation of the data. I can not upload the actual file, since it's way too big (60 MB). So a sample file should give you the idea.
Three sheets:
Data 1: some value lists multiple columns
Data 2: some value lists, again multiple columns
Desired result: the result as it should be listed.
example.xls. zip
Creating Single List With No Blanks From Multiple Columns?
May 2, 2014I typically use the following formula to create a list with no blanks, when I have a single column of data. Is there a way to do the same thing when you have multiple columns of data (side by side to make it simple) and you want to create a master list that gets rid of the blanks, and keeps them in the order they appear (by column)?
[Code] .....
For example, if I had data (with some blank cells randomly placed) in columns AM and AN, and I wanted a singe master list in one column of all non-blank cells in column AL, followed by column AM, etc.? Is there a formula to do this, or must I resort to a macro?
Calling Multiple Columns / Rows From Dropdown List
Feb 4, 2013I have a drop down list, using data validation, calling from a range name on a different sheet($1). Works great. I see my list of choices.
What I want to do next is for another named range on another sheet($2) to be called when I select one of the options from the drop down.
So say the drop down is in I71, when I pick an option from that list it will populate (B72, B73:G73, B74:G74...etc...) from a named range I have already defined on ($2).
Where would I put the (if I71=Range1, then paste this info into the (B72, B73:G73, B74:G74...etc...) on $1 ?
Compile List Of Specific Data From Multiple Columns
Aug 30, 2006I've been trying to create a formula that will take data with a specific status and put those in a list on a summary worksheet.
On my data worksheet I have two columns a category/ name column and a risk/status column, on my summary page I want to generate a list solely made up of names that have a specific status.
I've attached a sample.
Convert Long Address List To Multiple Columns
Nov 22, 2007I've been given a long list of mailing addresses in Excel in formatted as a single column, each address using three rows with no blank rows. Example:
John Doe
123 Maple St. #2
Cambridge, MA 02139
Jane Smith
321 Elm St. Apt 24
Austin, TX 34557
etc.
etc.
I need to get this data into the form ...
John Doe 123 Maple St. #2 Cambridge MA 02139
Jane Smith 321 Elem St. Apt. 24 Austin TX 34557
I would think this has been done, but I've searched the web and this forum without success.
Convert 2 Column List To Multiple Related Columns
Nov 28, 2007I have a long list of 2 columns containing data as follows:
col A col B
country1 date1-1
country1 date1-2
country1 date1-3
country2 date2-1
country2 date2-2
country3 date3-1
country4 date4-1
country4 date4-2
country4 date4-3
which I would need to move to get one row per country in column A with all related values from col B in multiple columns on a single Row, e.g....
Pivot Table To List Multiple Source Columns In Rows?
Jul 4, 2014I have a huge data set with survey data (sample attached: sample.xlsx). Column headings: survey questions Row headings: respondent identifiers (names) Row data: answers to questions (numeric values, only five answer options 1,2,3,4 or 5, no blanks)
I need to build that kind of pivot table:
Rows - list of particular questions (i.e. questions no. 3, 7 and 12) Columns - list of all diffrent answer entries (eventually, it will be 1,2,3,4 and 5) - it could also be questions in columns and answers in row (no difference) Values - count answer entries (i.e. how many answers "5" are on quesiton 3)
------
UPDATE: explanation added.
The reason I need pivot tables: i'll have to cross analyze multiple sets of questions. I'll have to do such cross-analysis 100+ times, so, writing a formula for each time does not quite work...
List All Sheets Names From Multiple Workbooks & Copy Transpose Certain Columns
Oct 7, 2009this may or may not be easy for some of you but its driving me nuts. Here's what I need to do.
1.Create a new workbook
2.Cycle through multiple workbooks and all worksheets within each workbook (all in the same folder). All workbooks will have the same structure.
3.Take the worksheet names and put them in rows
4.Take the data from the first column (column A) from the first worksheet of the first workbook and put them across the top (i.e. transpose the data). The first column will be the same in all workbooks so it doesn't matter where I get it from.
5.Copy the data from column E from each worksheet and paste them in rows in the new workbook (again transpose) corresponding to each worksheet.
6. Perform simple mathematical calculations at the end of each row.
I expect to have approximately 26 workbooks with a total of 7000 worksheets. In the target workbook (i.e. the new one), I expect there to be 7000 rows (corresponding to the 7000 worksheets) and about 260 columns.
The only reason I need to transpose stuff is because Excel 2007 doesn't have 7000 columns.
Here's an example of what it would look like.
Example worksheet (Input)-
Worksheet ABC
Col A Col E
1/1/2004 $25
1/8/2004 $30
1/15/2004 $15
Imagine another worksheet called LMN with the same ColA but different values in Col E.
Output workbook
ColA Columns B Column C Column D
Sheet 1/1/2004 1/8/ 2004 1/15/2004
ABC $25 $30 $15
LMN $xxx $yyy $zzz
Compare Multiple Column Of Data And List Out Common And Unique Component In Adj Columns
Jan 23, 2006I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
Hide / Unhide Columns In Multiple Worksheets Based On Item Chosen In Dropdown List?
Feb 26, 2014I am hoping to create a drop down list of months in one sheet, and when I select a certain month, columns in about 10 other worksheets in the same workbook will either hide or unhide columns...
The spreadsheet is laid out with columns (C-N) for each month in the year, for actuals, then columns for budget and budget variance (O-P), then YTD Actual, YTD Budget and YTD Variance. When I select September, for example, I want October-December to hide, and leave Jan-Sep unhidden, while keeping the budget, YTD and variance columns.
Is there a VBA code that can achieve this?
Counting Uniques ..
Apr 23, 2009I'm trying to figure out how to count the number of unique countries where there is an office.
So pretty much, I want to count the unique entries in Column A where there is an X in Column C.
Sum Uniques Across Worksheets
Feb 25, 2007I would like to sum uniques across worksheets. Below are the formulas that Domenic provided me and it works great but I could not modify the formula below that is in red to sum uniques instead.
=SUMPRODUCT(--(RIGHT(A3:A37,5)<>"Total"),--(MATCH(C3:C37&D3:D37,C3:C37&D3:D37,0)=ROW(INDEX(C3:C37,0))-ROW(A3)+1),B3:B37)
Define Names:
ArrayA:
=COUNTIF(OFFSET(INDIRECT("'"&Sheet1!$C$2:$D$2&"'!A3:A100"),ROW(INDIRECT("3:100"))-3,,1),"<>*")
ArrayB:
=N(OFFSET(INDIRECT("'"&Sheet1!$C$2:$D$2&"'!"&Col),ROW(INDIRECT("3:100"))-3,,1))
Col:
=CHOOSE(Pos,"A3:A100","B3:B100","C3:C100","D3:D100")
Pos:
=TRANSPOSE(MMULT((T(OFFSET(INDIRECT("'"&TRANSPOSE(Sheet1!$C$2:$E$2)&"'!B1:D1"),,COLUMN(INDIRECT("B:D"))-COLUMN(INDIRECT("D:D")),,1))="Net")*(COLUMN(INDIRECT("B:D"))-COLUMN(INDIRECT("B:B"))+1),TRANSPOSE(COLUMN(INDIRECT("B:D"))^0)))
=SUM(IF(ArrayA,ArrayB))
Count Of Uniques
Oct 16, 2007I want something to give me the total of different names in column A, I know the total is 8 but if I add another name I want Excel to add it up automatically in cell A2 in sheet #2.
View 9 Replies View RelatedLookup With Non-uniques In The Range
Mar 18, 2007looking for a way to look in a column for a value, and then spit out the highest value in corresponding cells in a column further over
so i want to look at all the classes associated with the same case, and have excel spit out the highest CscR no associated with those cells that do match..
yes i could do it manually, but my actual file has like 4000 samples in the first column.
Formula That Displays Uniques Only
Jul 14, 2008I have a list of names in a column that will be changing on a weekly basis (with some names repeating). I need to create a Named Range that only counts unique values from that list of names (dynamically changing every week).
View 11 Replies View Related