EXCEL

Go Footer

• 1.Open file format error
• 2.Combine row with same value of any column
• 3.Merge 2 spreadSheets with key in one column same data
• 4.Translate convert xls to csv

---

1. Open file format error

Excel > Data tab > New Query > From file > From CSV > UTF-8

2. Combine row with same value of any column

[1]. Install KuToolsForExcel
[2]. KuTools > Content > Advanced combine rows 

3. Merge 2 spreadSheets with key in one column same data

=VLOOKUP(Cell in current table, SheetName!range, number of table from range ,FALSE) =VLOOKUP(A1,SheetName!$A$2:$B$20,2,FALSE)

---

Sheet1
sku value
1     A
2     G
3     S
4     D
5     E
6     B

Sheet2
sku value 2
1     HE
2     GE
4     SE

---

Example for current table is Sheet 1. Choose C2 is current cell for form below =VLOOKUP(A2, Sheet2!$A$2:$B$4,2,FALSE)

---

A2 value is : 1 
B2 value is : A
Sheet2!$A$2:$B$4 : Range of Sheet2 data. 
2 : Number of column from A to B on Sheet2.

---

RESULT

sku value
1     A      HE
2     G      GE 
3     S      #NA
4     D      SE
5     E      #NA
6     B      #NA

---

4. Translate convert xls to csv

Go Header

=""""&A5&""""&","&""""&B5&""""

FOOTER

Go Header