Conas liosta luachanna uathúla a bhaint go dinimiciúil as raon colún in Excel?

Maidir le raon colún a mbíonn na luachanna ag athrú go rialta, agus ní mór duit na luachanna uathúla uile a fháil ón raon is cuma cén chaoi ar athraigh sé. Conas liosta dinimiciúil de luachanna uathúla a dhéanamh? Taispeánfaidh an t-alt seo duit conas déileáil leis.

Bain go dinimiciúil liosta de luachanna uathúla ó raon colún le foirmle
Bain go dinimiciúil liosta de luachanna uathúla ó raon colún le cód VBA

Bain go dinimiciúil liosta de luachanna uathúla ó raon colún le foirmle

Mar a thaispeántar thíos an scáileán, ní mór duit liosta luachanna uathúla a bhaint go dinimiciúil ó raon B2: B9. Bain triail as an bhfoirmle eagar seo a leanas le do thoil.

1. Roghnaigh cill bhán mar D2, cuir isteach an fhoirmle thíos agus brúigh an Ctrl + Shift + Iontráil eochracha ag an am céanna. (B2: Is é B9 na sonraí colúin ar mhaith leat na luachanna uathúla a bhaint astu, is é D1 an chill thuas ina bhfuil do fhoirmle suite)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2. Coinnigh ag roghnú cill D2, ansin tarraing an Láimhseáil Líon isteach chun na luachanna uathúla uile a fháil ón raon sonraithe.

Anois baintear na luachanna uathúla uile i raon colún B2: B9. Nuair a athrófar luachanna sa raon seo, athrófar an liosta luachanna uathúil go dinimiciúil láithreach.

An Roghnaigh Cealla Dúblacha & Uathúla fóntais de Kutools le haghaidh Excel is féidir leis cabhrú leat gach luach uathúil a roghnú agus aird a tharraingt air (na chéad dúblacha san áireamh) nó na luachanna uathúla nach bhfeictear ach uair amháin, chomh maith le luachanna dúblacha de réir mar a theastaíonn uait mar a thaispeántar thíos.
Íoslódáil Kutools le haghaidh Excel anois! (Conair saor in aisce 30 lá)

Bain go dinimiciúil liosta de luachanna uathúla ó raon colún le cód VBA

Is féidir leat freisin liosta luachanna uathúla a bhaint go dinimiciúil ó raon colúin leis an gcód VBA seo a leanas.

1. Brúigh Eile + F11 eochracha ag an am céanna chun an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Sa Microsoft Visual Basic d’Fheidhmchláir fuinneog, cliceáil Ionsáigh > Modúil. Ansin cóipeáil agus greamaigh an cód VBA thíos sa Modúil fhuinneog.

Cód VBA: Sliocht liosta de luachanna uathúla ó raon

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

nótaí: Sa chód, is é D2 an chill a bhfaighidh tú an liosta luacha uathúil. Is féidir leat é a athrú de réir mar is gá duit.

3. Téigh ar ais go dtí an bhileog oibre, cliceáil Ionsáigh > cruthanna > dronuilleog. Féach an pictiúr:

4. Tarraing dronuilleog i do bhileog oibre, ansin cuir isteach roinnt focal a chaithfidh tú a thaispeáint air. Ansin cliceáil ar dheis air agus roghnaigh Macra a shannadh ón roghchlár cliceáil ar dheis. Sa Macra a shannadh bosca dialóige, roghnaigh an CreateUniqueList sa Macra-ainm bosca, agus ansin cliceáil ar an OK cnaipe. Féach an pictiúr:

5. Anois cliceáil ar an gcnaipe dronuilleog, a Kutools le haghaidh Excel bosca dialóige aníos, roghnaigh le do thoil go bhfuil luachanna uathúla ag an raon a chaithfidh tú a bhaint, agus ansin cliceáil ar an OK cnaipe.

As seo amach, is féidir leat an chéim 5 thuas a athdhéanamh chun an liosta luacha uathúil a nuashonrú go huathoibríoch.

Earraí gaolmhara:

Na hUirlisí Táirgiúlachta Oifige is Fearr

Réitíonn Kutools for Excel an chuid is mó de do chuid Fadhbanna, agus Méadaíonn sé do Tháirgiúlacht 80%

Athúsáid: Cuir isteach go tapa foirmlí casta, cairteacha agus aon rud a d'úsáid tú roimhe seo; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
Cumaisc Cealla / Sraitheanna / Colúin gan Sonraí a chailleadh; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna / Colúin Dúblacha... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
Níos mó ná 300 gné chumhachtach. Tacaíochtaí Office / Excel 2007-2019 agus 365. Tacaíonn sé le gach teanga. Imscaradh éasca i d’fhiontar nó d’eagraíocht. Gnéithe iomlána triail saor in aisce 30 lá. Ráthaíocht ar ais airgid 60 lá.

Leigh Nios mo... Íoslódáil saor in aisce ... Ceannach ...

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!

Leigh Nios mo... Íoslódáil saor in aisce ... Ceannach ...

31 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
francesco · 8 months ago
how to make the vba code work for a range where another formula was used?
on column B I have a formula, referring to columns D and E.
If I use apply the code to column L (let's say), (obviously, properly modifying the cells in the code) the macro returns the formula applied to columns M and N... It works, then, but not as I want!
How to keep the values in column B?
thanks
- Reply
To post as a guest, your comment is unpublished.
A · 1 years ago
thank you very much
- Reply
To post as a guest, your comment is unpublished.
Charlotte · 1 years ago
I try the VBA Code and the formula. The code VBA is working very well but I can't keep a file with macro. But th problem is that I can't make the formula working. Did anybody have an idea ? Thank you
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Charlotte,
  Thank you for your comment. You can keep the file with macro for future use by saving the workbook as an Excel Macro-Enabled Workbook.
  For the formula problem, would you please provide a screenshot of your data? Thank you for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
Cameron · 1 years ago
I'm using that =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") formula which is great for one column but my data is spread across a range of columns and rows. Can I edit the formula to include the whole area? My data lives from AC4 to AR60...
- Reply
To post as a guest, your comment is unpublished.
Alexis · 2 years ago
Hi, thank you for your help.
Now, how can I have my values also sorted alphabetically? (I do not want to use the filter on my master table)
Should I use a COUNTIFS instead of COUNTIF?
Please HELP
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi Alexis,
  Sorry can't hep to sort the extracted value alphabetically at the same time with formula. Thank you for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
Charley · 2 years ago
I am trying to drag the formula down past my actual data so that I can input different sized data sets and not have to adjust anything. However, the last row after my actual data ends always returns a "0". I am using the unique values for something else in an adjacent column, and the 0 causes the last value to repeated (When I delete the 0 the value is no longer repeated). Any idea how to fix this? Also I am using Office 365 Business
- Reply
To post as a guest, your comment is unpublished.
parkerpress · 2 years ago
If I do this for a thousand row excel sheet on the latest version of Excel on a Mac, it never returns. The first row works, but when I duplicate down, excel goes into a compute mode that has not returned values for over two hours now.

Any thoughts on how to do this for large lists (up to 2k rows) that will return 50 or 60 unique values?

I mocked this up in the "Numbers" app, and it's working perfectly there, taking only a couple minutes to calculate. It's just taking so long in Excel that I wonder if it will ever complete. I plan on letting it "run" overnight to see what will happen.
- Reply
- To post as a guest, your comment is unpublished.
  Josh · 2 years ago
  
  Check your Calculating options. It needs to be set to automatic. File > Options > Formulas > Calculating options > Workbook Calculation (Automatic selection)
  
  Reply
To post as a guest, your comment is unpublished.
Matt · 3 years ago
How would you add a second variable? For instance, I want all the unique elements in one column that also share a similar value in another column. In your example, imagine a 3rd column titled "Department" that would have values like product, meat, etc. I realize those are all Produce, but hopefully you get my point. Would you modify the CountIF formula to a COUNTIFS or do you modify it in another way?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi Matt
  Please try this formula =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Yes","").
  Supposing the two compared lists are Column A and Column C, If the unique values stay only in Column A but not in Column C, it will be displayed Yes in column B; while if return nothing in Column B, it means corresponding value stays in both Column A and Column C.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  jyotiba mali · 2 years ago
  
  Thanks for the reply.. but hot to pull out that unique value if it displayed YES.. could you please advice me the formula to pull the unique value in different column.
  
  Reply
To post as a guest, your comment is unpublished.
Zac · 3 years ago
How would you add multiple criteria, such as if you only wanted to add to the dynamic list if the date was just 9/12?

I'm trying "&" in the MATCH formula, but it's not working.

For example, based on your example:
=IFERROR(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"")
This throws an error or creates duplicates.

Alternatively, I've read that "+" might work, although I can't get it working. Or using SMALL.

Ideas?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Dear Zac,
  Sorry I can’t help with this, you can post your question in our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional.
  
  Reply
To post as a guest, your comment is unpublished.
Mike · 3 years ago
Hi Crystal,
I am trying to use the VB version of the unique values list and running into an issue.
The range that I want to create a unique values column from is all formulas that refer to different tabs.
How does one get the value to transfer over instead of the formula?
- Reply
- To post as a guest, your comment is unpublished.
  Odette · 3 years ago
  
  I have the same problem, except that my formula refer to column names and cannot convert to absolute.
  How do I change the vba to paste the values and not the formula?
  
  Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Dear Mike,
  Please convert your formula references to absolute, and then apply the VB script.
  
  Reply
To post as a guest, your comment is unpublished.
Jones · 3 years ago
Any tips on getting the VBA option to work with Excel 2016 for macOS? I have followed the steps; however, when I run the macro, nothing happens at all. Thanks!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Daer Jones,
  Please try the below VBA code and let me know whether it works for you. Thank you!
  
  Sub CreateUniqueList()
  Dim xRng As Range
  Dim xLastRow As Long
  Dim xLastRow2 As Long
  Dim I As Integer
  ' On Error Resume Next
  Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
  If xRng Is Nothing Then Exit Sub
  On Error Resume Next
  xRng.Copy Range("D2")
  xLastRow = xRng.Rows.Count + 1
  ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
  xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
  For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
  ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
  Next
  End Sub
  
  Reply
To post as a guest, your comment is unpublished.
Ryan · 3 years ago
Also, for whatever reason, the original formula provided:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

returns a "circular reference" warning and will not calculate..
- Reply
- To post as a guest, your comment is unpublished.
  Andrew · 2 years ago
  
  I've had this happen before - my fix was that I was entering the formula into the cell D1 (equivalent in the worksheet I was using). Whichever cell the $D:$1 corresponds to you need to be entering it in the cell below - D2. Apologies if that's not why you got the error
  
  Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Dear Ryan,
  Which Office version do you use? The formula works well in my Office 2016 and 2013.
  
  Reply
To post as a guest, your comment is unpublished.
Ryan · 3 years ago
Hello, and thank you for your help.

I need exactly this functionality, but my list of "unique values" needs to extend across columns instead of rows, so the expanding list down the rows won't work for me.

How can I modify this formula in order to make the "unique values" list expand as I drag it across the columns?

Offset()?
Transpose()?
Indirect() with a string of absolute references concatenated with a reference to the column instead of row?

Thanks again!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Dear Ryan,
  This formula =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter can help you to solve the problem.
  See below screenshot:
  
  Reply
To post as a guest, your comment is unpublished.
v.urala@gmail.com · 3 years ago
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - found this to work from another site...

Use the Ctrl+Shift+Enter to get the array function (curly braces). Drag copy-paste the formulas until the #NA is shown. My data set was in Column-Q, it was compared to see if it existed in the unique's list in Column-V, which continually stretches along this same column.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Good Day.
  Please list all unique values of column Q with the abobv formula, and then use his formula =IF(D2=V1,"Match","No match") to compare if the uniques in cilumn Q compareing to column V in the same row.
  
  Reply
To post as a guest, your comment is unpublished.
Outdated · 3 years ago
This formula is outdated and doesn't work. I literally just set this exact excel sheet up to see if I could get this formula to work and it doesnt.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Hey guy,
  Which Office version do you use?
  
  Reply
To post as a guest, your comment is unpublished.
PJ · 4 years ago
I am decent at excel but I am really trying to wrap my head around how and why the above formula works (it works for what I am using it for but I must understand why). I get a little confused using arrays sometimes so any explanation in idiots terms would be extremely helpful

Regards
- Reply
To post as a guest, your comment is unpublished.
Eric · 4 years ago
Thank you for the tutorial. Using the formula method, how would you alter the formula if you wanted to add a category qualifer? Say in column C you distinguish whether the item is a fruit or a vegetable. How would you change the code to only sort the unique fruits and exclude the vegetables? I tried replacing COUNTIF with COUNTIFS, using the second countifs criteria of (LIST RANGE,"CATEGORY") but it returns blank. Would I need to expand my array and incorporate VLOOKUP?
- Reply

Logáil isteach do chuntas

Athshocraigh Pasfhocal