Hello!

I have two columns which I've converted into tables.

I need a formula to fill in a third column, on a second sheet, which will pull all the unique values from the two tables.

I've got this array formula going, but it only works for one of the columns.

=INDEX($D$11:$D$18,MATCH(0,COUNTIF($H$10:H10,$D$11:$D$18),0))

Thanks in advance!

I have two columns which I've converted into tables.

I need a formula to fill in a third column, on a second sheet, which will pull all the unique values from the two tables.

I've got this array formula going, but it only works for one of the columns.

=INDEX($D$11:$D$18,MATCH(0,COUNTIF($H$10:H10,$D$11:$D$18),0))

Thanks in advance!

- How to get unique value from two columns in a third colum
- Extracting Unique data from two columns
- Unique value from two cols from two tabs?
- New Column With Unique Values From Adjacent Column
- List unique Values from different columns: How to...
- Append Values From Two Columns On Separate Sheets To One Column On Another Sheet
- Counting Text Values From Two Columns
- Return unique values from a list
- How do I get unique values from 2 columns?
- Formula to count values in two columns
- Capture unique values from 1 column and populate the result into another column
- Finding unique values from a filtered range
- How to obtain values from other columns in excel ??
- Sorting Combobox filled with unique value from Column
- Search a Value from Two Columns, Find the corresponding value in third column
- Auto Retreive Unique Values from one column to another sheet (WITHOUT ADVANCED FILTER
- Counting unique values between two columns
- Search a Value from Two Columns, Find the corresponding value in third column
- Extract Unique Values from an Array based on Criteria
- SumProduct unique values across 2 columns
- Unique Values from Four Columns-Urgent
- Summing Unique Values from 4 Columns
- Extract unique items from a column of items dynamically
- VBA- create a dropdown in worksheet1 based on unique values from column on worksheet2

Please help me out on the same

Thanks for you help and ideas!

Gary

the formula I would like to use is something like;

=sum(if(frequency("tab1!H:H"&"tab2!H:H","tab1!H:H"&"tab2!H:H")>0,1))

of course this does not work. Can someone help me make the combined columns I am trying to do work?

Thanks

'some' answers but not the thing I need.

I got a few workbooks with a lot of worksheets. For almost every workbook

there needs to be a list of unique values coming from different worksheet

(always in the same workbook). I would like to get a clue on how to do that.

So suppose worksheet 1, and worksheet 2 I would like to create a list on

worksheet 3 gathering all unique values from columns B (of both worksheet 1

and 2).

So far I have been directed towards VBA, some commercial add-ins and the

classic copy and paste.

But you can imagine that I do not like to pay for such a (in my opinion)

basic function, nor copy over 20.000 rows (risking a lot of errors and

'forgotten' rows) and VBA well... I'm not good at it...

I tried to filter data, but can only do that for a single column. And I

really need a list, so dropdown won't do...

Suggestions very much appreciated.

Sheet1/Column 1

123456

234567

345678

456789

567890

Sheet2/Column 1

654321

765432

Sheet3/Column 1(Desired Result)

123456

234567

345678

456789

567890

654321

765432

I'm guessing this would require VBA code to accomplish, unfortunately something I am not very experienced with. Any help would be greatly appreciated.

Column B has a value of either "Open" or "Closed".

Column C has a value of either "High", "Med", or "Low".

I want to count the number of rows that are "Open" "High", "Open" "Med", and "Open" "Low".

I've been studying a number of different formulas, but I can't seem to get my head wrapped around this functionality.

Thank you to anyone who may be able to help me with this.

Thanks,

Jon

BuzzG

2,3,4,5 & 7 and B has 1,2,3,4,and 5, I need something to tell me that B

doesn't have 7. I expect to run through it again so that I see that A doesn't

have 1. I do not wish to enter in the data to search for, but rather have a

column comparison and kickout the differences (similar to a diff command on

Unix for those that know it).

Any help would be appreciated. The advanced filter doesn't do what I need

as it would give me all values.

Thanks,

Kelly

Is it possible to count values from two columns. I know that to count

values from one column the formula is =COUNTIF(B2:B20,"NEW"), but I need to

count a value from a second column also. For example:

Column A Column B

NEW critical

NEW critical

CLOSED major

NEW major

I need a formula that will count the value for "NEW" in Column A that are

"critical" in Column B. The count for this example should be 2. The count

for NEW - major should be 1 and CLOSED - major should be 1. Can anyone help?

THANKS!!

I have values in COlumn A, probably about 50K rows, The total unique values on the column A is only about 27. I need to capture all those unique values and paste them in column B. I didn't want to do "Advance filtering" since the workbook has many macros's on it that the user simply click on. Im thingking of creating a loop and compare values already pasted in Column b but then that will take too much time to complete the loop. Is there any easier or yet more efficient way to accomplish this?.

a unique value using the autofilter method. I now want to obtain the

unique values from another column within this range. When I try and

apply and advanced filter to generate the unique values it automtically

takes off the advanced filter ruining my filter range.

Is there a way I can do this while still using the advanced filter

method?

Plllease helpp. what is the method to concatenate values from two columns??

I want to obtain the values from three different columns in excel and want to check if those values are concatenated and present exactly in the fourth coulmn .

For eg.If column 1 has-PT50

column2 has-456

and column3 has -abc

in column 4 must contain PT50456abc.Otherwise itmust be colored in red. can we use '&' for it.? Please find the code.Its showin error.

Sub newone() Dim res As String For i = 4 To 8 res = "PT50" & Cells(i, 3).Value&Cells(i, 5).Value&Cells(i, 15).Value If Cells(i, 16).Value <> res Then Cells(i, 16).Select With Selection.Font .Color = -16776961 .TintAndShade = 0 End With End If Next i End SubHelp

I've put together this code (with alot of help from this site may i add!), it uses the column number to populate a combobox with the all the unique values within the column. I want to apply the bubble sort method to ensure that the items are arrange alphabetically (or numerically). I just dont know how to mix the two together...

Private Sub UserForm_Initialize() Dim ws As Worksheet Dim dic As Object, x, r As Range Dim headcolnum As Long Set ws = Worksheets("DATA") Set dic = CreateObject("Scripting.Dictionary") headcolnum = "20" With ws For Each r In .Range(Cells(1, headcolnum), .Cells(65536, headcolnum).End(xlUp)) If Not IsEmpty(r) Then If Not dic.exists(r.Value) Then dic.Add r.Value, Nothing End If End If Next End With x = dic.keys Me.ComboBox1.list = x End SubAnd the bubble sort, from a previous post (from RoyUK):

'Two loops to bubble sort For i = 1 To cColl.Count - 1 For j = i + 1 To cColl.Count If cColl(i) > cColl(j) Then 'store the lesser item vTemp = cColl(j) 'remove the lesser item cColl.Remove j 're-add the lesser item before the 'greater Item cColl.Add vTemp, vTemp, i End If Next j Next i 'fill combo For i = 1 To 4 For Each vItm In cColl Controls("ComboBox" & i).AddItem vItm Next vItmI don't think its very difficult to do, I just cant make it work, so any help would be appreciated!

Thankyou in advance,

Jack

Please help.

I have a value in a cell G1 = 1000.

Column B1 has value 99,35,48,25,96 etc (going down)

Column C1 has 65, 1000, 86,45,32 etc (Going down)

Column A1 has 101,102,103,104,105 etc.

The value in G1 is dynamic means it can be 1000 or 86 or 48 or 99 etc.

Please help in find a formula such that when G1=1000, it has to search for 1000 in Column B1 and C1, and give the corresponding row value in Column A1 in another cell F1. In this case, I should be getting 102 in cell F1. If G1=45, the value from A1 should be 104 in cell F1. If its 25, A1 should be 104 in cell F1. If its 99, A1 should be 101 in cell F1.

Please Help.

Thanks a lot. Been bugged down for two hours.

Also, if there are multiple cells having the same value that is seeked, the value in F1 should be able to show the corresponding value in Column A for the First Exact Match. For Eg, if Column B1 has 1000,1000,1000,1000,1000 and Column C1 has 1000,1000,1000,1000,1000, the Cell F1 should show 101 in F1. If its 89,1000,1000,89,100 in B1 and 85,100,1000,1000,1000, F1 should be able to show 102.

Thanks a lot.

Sorry been trying to upload the file but I am not able to do it from my system. Will upload once i get home from office.

Please find attached the excel sheet. Thanks..

The advanced filter will not work for me, because I need the list of unique values to auto-update so that I can create a pivot table from the sheet named "Combo".

Is there a Macro that can be used for this? Or a formula that does not require immense processing times?

Thanks!

B L

Please help.

I have a value in a cell G1 = 1000.

Column B1 has value 99,35,48,25,96 etc (going down)

Column C1 has 65, 1000, 86,45,32 etc (Going down)

Column A1 has 101,102,103,104,105 etc.

The value in G1 is dynamic means it can be 1000 or 86 or 48 or 99 etc.

Please help in find a formula such that when G1=1000, it has to search for 1000 in Column B1 and C1, and give the corresponding row value in Column A1. In this case, I should be getting 102. If G1=45, the value from A1 should be 104. If its 25, A1 should be 104. If its 99, A1 should be 101.

Please Help.

Thanks a lot. Been bugged down for two hours.

I have a large spreadsheet with two columns of data (one name and the other type - see attached sample spreadsheet). I am looking to extract unique values from the name column if the type is "Adult". Next, I want to then alphabetize the unique values. How would I do that in Excel using only formulas (no VBA).

Many thanks in advance.

From the example data below, which summarises a series of meetings that we have hosted with different firms attending, i'm trying to get a formula to work out how many unique 'Group' meetings there were.

i.e. from the data below, 4 firms attended a group meeting at 12:00 on 17/10/2007, so that needs to be counted as one unique group meeting.

I can use sumproduct and countif (or frequency) to work out how many unique values there are within one column, but I can't work out how to sum up the number of unique entries across two columns (date and time).

Please note that this needs to be done as a sumproduct because the sample data below comes from a larger data set.

Any thoughts?

A B C D

Date StartTime MeetingFormat FirmName

17/10/2007 09:00 One-on-one Firm 1

17/10/2007 10:30 One-on-one Firm 2

17/10/2007 12:00 Group Firm 3

17/10/2007 12:00 Group Firm 4

17/10/2007 12:00 Group Firm 5

17/10/2007 12:00 Group Firm 6

17/10/2007 13:30 Group Firm 7

17/10/2007 13:30 Group Firm 8

17/10/2007 13:30 Group Firm 9

17/10/2007 15:00 One-on-one Firm 10

06/03/2008 09:00 One-on-one Firm 11

06/03/2008 10:30 One-on-one Firm 12

06/03/2008 12:00 Group Firm 13

06/03/2008 12:00 Group Firm 14

06/03/2008 12:00 Group Firm 15

06/03/2008 12:00 Group Firm 16

06/03/2008 12:00 Group Firm 17

06/03/2008 13:30 Group Firm 18

06/03/2008 13:30 Group Firm 19

06/03/2008 13:30 Group Firm 20

06/03/2008 13:30 Group Firm 21

06/03/2008 15:00 One-on-one Firm 22

16/04/2008 09:00 One-on-one Firm 23

16/04/2008 10:45 One-on-one Firm 24

16/04/2008 12:00 Group Firm 25

16/04/2008 12:00 Group Firm 26

16/04/2008 12:00 Group Firm 27

16/04/2008 12:00 Group Firm 28

16/04/2008 12:00 Group Firm 29

16/04/2008 12:00 Group Firm 30

16/04/2008 13:30 Group Firm 31

16/04/2008 13:30 Group Firm 32

16/04/2008 13:30 Group Firm 33

16/04/2008 13:30 Group Firm 34

16/04/2008 13:30 Group Firm 35

16/04/2008 15:30 One-on-one Firm 36

I have Four Columns, which has duplicate values in it. I need a macro where it searches for unique values and pastes in the new worksheet.

Please find attached data sheet it has two tabs one with data and second one with the desired output. I have data in about 5000 rows

Thanks for your help in advance!!!

BR

Uday

So if 2 rows in different parts of the spreadsheet have as an example:

Row 1 - Property 17851 - Line of Business 11 - Department 100 $ 500.00

Row 300 - Property 17851 - Line of Business 11 - Department 100 $ 500.00

I want the $ amount in column I add up to $1000 and be able to show that in another tab so it shows 17851-11-300-$1000.00

The complicated part is I need to do that for each unique code combinations of F Through H.

Any help here would be greatly appreciated.

I have a list of cost center codes (SMH1020, 5K20...). They are from an export from another database. There are numerous duplicates in the list which is very long. I want to extract the unique values from that list(Column A) and place them in a different list(Column B). I want this to be dynamic so if I past in a different list of values it will automatically update the unique list.

I have tried auto filter but it is not a dynamic function. You have to redo it every time you add something to the list.

I have tried other formulas listed elsewhere but they don't seem to work.

Any help would be appreciated.

I have about 10,000 rows in columnA sheet2 that I want to automatically filter on unique values and then create a dropdown in cell B1 on Sheet1 with those unique values. I want to accomplish this using vba with out having the data validation reference any cells if possible. Can you have data validation reference an array that you create based on unique values in a column on another sheet? Help would be greatly appreciated! This will be an addition to a long macro so the smaller/less time consuming the better.