Office usage record (i)

Tag: Office macro Excel function Excel value Excel find Excel comparison

When the company's finances are not proficient in office, you have to give support! Office has not been specifically researched, it is also 1.1 points to find, to try; the commands and macros used for Office are now recorded:

Cases:

Table I.					Table II
Number	Value One	Value Two	Numerical three		Number	Value One	Value Two	Numerical three
a001	12345	34567	78901		A002	6666	7112	2323
A002	123	5555	6666		A003	3334	5678	9867
A003	7777	812	345		a004	123	4445	66443

1, take the left and right values

Take left =right (b2,2) "Select two digits to the left of the B2 cell" =45

Take right =left (b2,2) "Select B2 cell right two digits" =12

Value not after two-bit =left (B2,len (B2)-2) =123//Where Len (B2) is to display the current character length

2. Data merging

Add the cells in Table I, table two columns, shown in table Three, table b2+ table two b2= table three. B2

Your own method: First use VLOOKUP to extract the corresponding cell values, then the problem is that the value will be displayed correctly, if there is no value will display an error message, the results will be added to the error message, so you have to determine the value.

Table Three. B2 = IF (ISNA (VLOOKUP (A2, ' table One '! $A $: $D $4,2,0)), 0,vlookup (A2, ' table One '! $A $: $D $4,2,0)) +if (ISNA (VLOOKUP (A2, ' table two '! $A $: $D $ 5,2,0)), 0,vlookup (A2, ' Table II '! $A $: $D $4,2,0))

This method is a bit long, but it looks very clear, can think of, a look is rookie write, the following is the master wrote,

Table Three. B2 = SUMIF (table one!) A:A,A2, table one! B:B) +sumif (table two!) A:A,A2, table two! B:B)

Simple and clear, it is very useful.

3, VLOOKUP data search

This function is often used, and sometimes it's just a matter of time.

Lookup table One b4=vlookup (a4,a2:d4,2,0)//Find the value of the 2nd column in the A2:b4 area of the A4

4. Same Column data filter

Example: A number column appears with multiple identical values, such as multiple a001, a002, want to keep only one, other filters out

Auxiliary column =if (COUNTIF (A$1:A1,A1) =1,a1, "")

5, from column A to column B column, if the A1 and B1 are different, then B1 down one block and then compare with the A2, the same is skipped compared to the next cell, and will be different fill red value 0

Sub Test ()
Dim A, B, C as Integer
A = Range ("a65536"). End (Xlup). Row
For b = 1 to a
If cells (b, 1) <> Cells (b, 2) Then
Cells (b, 2). Select
Selection.insert Shift:=xldown, Copyorigin:=xlformatfromleftorabove
ACTIVECELL.FORMULAR1C1 = "0"
With Selection.interior
. ColorIndex = 3
. Pattern = Xlsolid
. Patterncolorindex = xlautomatic
End with
End If
Next
End Sub

Online search, very useful, such as two of the same report comparison, want to find two reports missing a few lines, use this can.

6. Delete the same data in both columns

Sub Removesame ()
Dim col1, col2 as String
col1 = "A"
col2 = "B"
Dim Row1, Row2, I, J as Integer
Dim finded as Boolean
Row1 = ActiveSheet.Range (col1 & "65536"). End (Xlup). Row
Row2 = ActiveSheet.Range (col2 & "65536"). End (Xlup). Row
For i = 1 to Row1
finded = False
If (Range (col1 & i) <> "") Then
For j = 1 to Row2
If (Range (col1 & i) = Range (col2 & j) Then
Range (col2 & j) = ""
finded = True
End If
If (finded) Then
Range (col1 & i) = ""
End If
Next
End If
Next
End Sub

Sometimes you want to delete the same data from two reports, you can use this macro

This article is from the "90" blog, please be sure to keep this source http://90909090.blog.51cto.com/3182039/1597156

Office usage record (i)