시간과 공간 그리고 진실의 방

에러에서 npm과 node가 latest_version 인지 확인하라는 메세지가 나온다.

환경단에서 gauge.isEnabled() 와 같이 등장할 수 있다.

1.

$ npm install latest-version

전체 환경인 상태에서 npm의 버전을 latest 로 올려준다.

또한, package-lock.json 이 found 되는 경우에도 라이브러리 설치에 여러모로 영향을 미치기 마련인데, 

package 관리자를 yarn 을 쓴다고 해서, yarn 으로 무조건 조져도 되는 것도 아니고... 

hang up 걸린 상태가 종종 등장하기 때문에, 

하드웨어 패키지 내부에서 npm install 대신에 yarn 을 설치해서 잡아줬어야 했고

전체 환경에서는 windows-build-tools 와 node-gyp 를 잡아줘야 했다.

어쨋든 @4.0.0 으로 설정하게 가버리면 완전 나가리가 된다.

결국 앞전에서 개삽질 하던것은 실패했다.

그래서 에러에 대해서 읽어보았다.

1.

그래 에러형이 까라면 까야지.

버전도 맘에 안들고 여러모로 맘에 안드니 "Desktop development with C++ "  Workload 환경이 맘에 안든다는 이야기고, 빡치면 문서 찾아보라그래서 들어가봤다.

2.

github.com/nodejs/node-gyp#on-windows

역시 킹허브 갓허브에는 해외 개발자들 답게 문서정리를 굉장히 잘해놓았다.

처들어가보니, 환경부터 수정해보란다.

On Windows

Install the current version of Python from the Microsoft Store package.

Install tools and configuration manually:

• Install Visual C++ Build Environment: Visual Studio Build Tools (using "Visual C++ build tools" workload) or Visual Studio 2017 Community (using the "Desktop development with C++" workload)
• Launch cmd,

$ npm config set msvs_version 2017

If the above steps didn't work for you, please visit Microsoft's Node.js Guidelines for Windows for additional tips.

To target native ARM64 Node.js on Windows 10 on ARM, add the components "Visual C++ compilers and libraries for ARM64" and "Visual C++ ATL for ARM64".

3. 

응 형 수정해봤는데, 안되더라고... 근데 위에 보니까 링크를 또 남겨놨더라?

Visual Studio Build Tools 를 깔거나 (이건 실패했잖아), 그래서 Visual Studio 2017 Community 를 깔라길래 또 처들어갔다. (그러니까 대충 기반환경 만들어놓고 쌉쳐라 이런거지 뭐)

4.

느낌상 이번엔 되지 않을까? 싶은데 되야지 안되면 진짜 죽.. 아니야

$ npm install --global --production windows-build-tools

 windows-build-tools 를 설치하다보면, 도중에 stuck (hang up)이라는 현상이 발견하게 되는데, 

대부분 해외 포럼에서는 windows-build-tools@4.0.0 을 설치하라고 하지만

문제는 난 그 다음에 node-gyp 를 설치하고 나서, working tree 의 dependency 때문에 작동을 잘 할 수 없어서

electron이 잘 작동하지 않고 에러를 뱉기 시작했다.

그래서 그냥 에러가 써있는대로 가서 폴더를 날려버렸다.

1.

..\Program Files (x86)\Microsoft Visual Studio\VS2017 

에 들어가면 맘에 안드는 녀석ㅇ ㅣ있는데 그녀석 부터 일단 날려버리고...

2.

그 다음에 명령어를 때려박아준다.,

$ npm remove windows-build-tools@4.0.0

install 의 반대말은 remove 아니겠는가? 그래서 때려박았더니 아주 잘 지워진다.

그것도 dependency 까지 확인하면서 친절하게 지워준다.

원래는 npm cache clean 을 넣다가도, 맘에안든다고 --force 를 주라길래 그렇게 했었는데도 안되길래 이렇게 넣어버렸다.

위에 @4.0.0 을 표시해준건 VS2015 환경에 대한 세팅을 돌려버리기 위해서다

3.

$ npm remove windows-build-tools

그러고 나서 또 꼬여있을 녀석을 위해서 한번더 지워준다

4.

그리고 나서, Stuck 구간이 발견되는 Python 2.7도 Uninstall 해준다.

5.

$npm install --global --production windows-build-tools

6.

그리고 다시 명령어를 때려박아서 위처럼 얌전히 기다린다. 보통 4분정도면 pass 된다고 하는데, 안되면 빡칠 것 같다.

This tutorial describes multiple ways to extract a unique or distinct list from a column in Excel. This post also covers a method to remove duplicates from a range. It's one of the most common data crunching task in Excel.

Scenario

Suppose you have a list of customer names. The list has some duplicate values. You wish to extract unique values from it. Unique values would be a distinct list. To make it more clear, unique values are the values that appear in a column only once.

Sample File

Click on the link below and download the excel file for reference. We will use this workbook to demonstrate methods to find unique values from a column.

Extract unique values from a column

The dataset contains 13 records. Data starts from cell B3 and ends with cell B15. Header of the list exists in cell B2.
LIST1 Jhonson Jhonson Jhonson Dave Dave Deepanshu Sohan Neha Deepanshu Neha Deepanshu Neha Sohan
See the snapshot of actual data in images below.

4 Methods to Extract Unique Values

1. Advanced Filter
2. Index- Match Array Formula
3. Excel Macro (VBA)
4. Remove Duplicates

The above methods are explained in detail in the following sections.

Solutions

1. Advanced Filter

Follow the steps shown in the animation below

Steps to extract unique values using Advanced Filter

1. Go to Data tab in the menu
2. In Sort and Filter box, Click Advanced button 
3. Choose "Copy to another location"
4. In "List range :" box, select a range from which unique values need to be extracted (including header)
5. In "Copy to :" box, select a range in which final output to be put
6. Check Unique records only
7. Click Ok

2. INDEX-MATCH (Array Formula)

FORMULA 

=IFERROR(INDEX($B$3:$B$15, MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0)),"")

Hit CTRL+ SHIFT + ENTER to confirm this formula as it's an array formula. If done correctly, Excel will automatically place curly braces {...} around the formula.

After placing curly braces, the above formula would look like this :

{=IFERROR(INDEX($B$3:$B$15, MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0)),"")}

Copy the above formula and paste it into cell D3. And paste it down till the cell D12 (Select the range D3:D12 and press Ctrl+D).

HOW TO USE

The functioning of this method is visible in the animated image below.

Version 2 : IF BLANK VALUES IN A LIST

Suppose there are missing or blank values in your list from which you want to extract unique values. In this case, you need to tweak your formula. The modified formula is explained below -

FORMULA

=IFERROR(INDEX($B$3:$B$15, MATCH(0,IF(ISBLANK($B$3:$B$15),1,COUNTIF($D$2:D2, $B$3:$B$15)), 0)),"")

Copy the above formula and paste it into cell D3. And paste it down till the cell D12 (Shortcut key : Ctrl+D).

You need to press CTRL+ SHIFT + ENTER  to submit this formula. It is different than the standard ENTER button to enter a formula. If you do it right, MS Excel will put curly braces {...} around the formula. It would view like this :

{=IFERROR(INDEX($B$3:$B$15, MATCH(0,IF(ISBLANK($B$3:$B$15),1,COUNTIF($D$2:D2, $B$3:$B$15)), 0)),"")}

How this formula works

First we need to understand the meaning and use of array formula.

Array formula allows you to process a certain operation on multiple values using a single function. In other words, we can perform some calculation on more than one value without doing it manually on each cell. For example, you want to multiply each value by 5 and then sum all of the returned values.from multiplication. Suppose following values are stored in cell A1:A3
25 35 45
Enter the formula  =SUM(A1:A3*5) with CTRL+SHIFT+ENTER. It returns 525. In this case, it is doing matrix multiplication and then adds all the numbers.

Functioning of Formula : Step by Step

Step 1 : COUNTIF($D$2:D2, $B$3:$B$15)

Syntax : COUNTIF(range, condition)
It counts the number of cells within a range that meet the given condition

COUNTIF($D$2:D2, $B$3:$B$15) returns 1 if $D$2:D2 is found in $B$3:$B$15 else 0.

For example, for the second distinct record Dave, the formula becomes COUNTIF($D$2:D3, $B$3:$B$15). It is searching values D2 and D3 in the range B3:B15. The array becomes 

={1;1;1;0;0;0;0;0;0;0;0;0;0}. It is 1 when values of D2 and D3 are found and 0 where it is not found.

Step 2 : In this step, we are checking the position of item that has an array value 0 in Step I.

Syntax : MATCH(lookup_value;lookup_array; [match_type] 

It gives the relative position of an item in an array that matches a specified value.

MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0) returns 4 for the second distinct value. It is 4 because the value Dave is placed in the fourth position of the list. [Also see 0 is the fourth value of the step 1 array - {1;1;1;0;0;0;0;0;0;0;0;0;0}]

Step 3 : In this step, we extract the desired distinct value. The INDEX function helps to achieve it.

Syntax : INDEX(array,row_num,[column_num])

The INDEX function returns the reference of cell meeting row and column number in a given range.

INDEX($B$3:$B$15, MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0)) returns Dave.

Tutorial : Excel Array Formula with Examples

3. MACRO (Advanced Filter)

It's an excel macro to find distinct values from a column in Excel. In this method, we are using the same logic as we have done in first method i.e. Advanced filter. Here, we are applying advanced filter via excel macro rather than doing it manually.

VBA CODE

How to create unique list using macro

1. Go to excel sheet where data exists.
2. Press Alt + F11 to open VB editor window

3. Go to Insert menu >> Module. It will create a module.

4. In the module, copy and paste the above vba code into the window
5. Close VB Editor Window
6. Go back to your sheet
7. Press Alt + F8. Select CreateUniqueList under Macro name box and Hit Run button.

Download the workbook
Customise Macro Code

The following are two most frequently asked questions about above excel macro with solutions. If you have any other question regarding the macro, post your question on comment box below.

Q. How to paste unique values to another existing worksheet?

Change ActiveSheet.Range("D2") to Sheets("newsheet").Range("D2")

In the above code, change "newsheet" to the name of the existing sheet wherein you want to paste unique values.

Q. How to paste unique values in a new worksheet?

Use the program below. It will paste distinct values to a new worksheet named "mysheet". You can change it to any name you want -

Option Explicit
Sub CreateUniqueList()
Dim lastrow As Long
Dim ws As String
ws = ActiveSheet.Name
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Sheets.Add.Name = "mysheet"
Sheets(ws).Range("B2:B" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Sheets("mysheet").Range("D2"), _
    UNIQUE:=True
End Sub

I have added one more way to extract unique values from a range [Updated : June 2016]

4. Remove Duplicates Option 

The most easiest way to extract unique values from a range is to use "Remove Duplicates" option. See the snapshot below -

Warning : If you want to keep your original data (not overwrite unique values), make a copy of it (Paste original data to another range or tab) Otherwise original data would be removed.

Steps to remove duplicates

Select range >> Go to Data option >> Click on Remove Duplicates >> Select the column that contains duplicates >> Ok

Important Note :

1. If you have multiple columns in a range and you want to remove duplicates based on a single column, make sure only the column that contains duplicates is selected.

2. If you want to remove duplicates based on all the columns (whole row), make sure all the columns are selected.