|
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Hey,
Plz Need Help In This Loopup Technique
Sheet2
A
B
C
D
E
F
G
H
I
J
K
L
M
2
Formula
My Range
Tables List
Data
Results
Table1
Table2
Table3
3
#N/A
A1:A9
Table2
A1
100
A1
1
A1
100
A1
1000
4
A2
101
A2
2
A2
101
A2
1001
5
A3
102
A3
3
A3
102
A3
1002
6
A4
103
A4
4
A4
103
A4
1003
7
A5
104
A5
5
A5
104
A5
1004
8
A6
105
A6
6
A6
105
A6
1005
9
A7
106
A7
7
A7
106
A7
1006
10
A8
107
A8
8
A8
107
A8
1007
11
A9
108
A9
9
A9
108
A9
1008
12
A10
10
A10
109
A10
1009
13
A11
11
A11
110
A11
1010
14
A12
12
A12
111
A12
1011
15
A13
13
A13
112
A13
1012
16
A14
14
A14
113
A14
1013
17
A15
15
A15
114
A15
1014
Spreadsheet Formulas
Cell
Formula
A3
=VLOOKUP(B3,C3,2,0)
Excel tables to the web >> Excel Jeanie HTML 4
My look Up Formula Is In Cell A3
In Cell B3 The "Lookup Values" It's The Range From "A1:A9"
In Cell C3 The Data List Including Table 1,2,3 "Table Array"
My Results Of Shoul Be In Cells F3:F11
How Can I Apply This
Thanks In Advance
Similar Excel Video Tutorials
VLOOKUP 11 Unusual Examples
- See these 11 VLOOKUP tricks: 1.VLOOKUP algorithm 2.VLOOKUP, Named Ranges, Exact Match, COLUMNS function& Data Validation List 3.Com ...
VLOOKUP Categories
- See when to use IF and when to use VLOOKUP depending on your category setup. See when the VLOOKUP function beats the IF function, but only if categori ...
IF AND vs. VLOOKUP
- See how to use the IF, AND and VLOOKUP functions to deliver the words Bid, No Bid, or Alert to a cell. Is VLOOKUP better than IF AND? ...
Similar Topics
Hey,
Plz Need Help In This Lookup Technique
Sheet2
A
B
C
D
E
F
G
H
I
J
K
L
M
1
2
Formula
LookUp Values
Table Array
Data
Results
Table1
Table2
Table3
3
#N/A
A1:A9
Table2
A1
100
A1
1
A1
100
A1
1000
4
A2
101
A2
2
A2
101
A2
1001
5
A3
102
A3
3
A3
102
A3
1002
6
A4
103
A4
4
A4
103
A4
1003
7
A5
104
A5
5
A5
104
A5
1004
8
A6
105
A6
6
A6
105
A6
1005
9
A7
106
A7
7
A7
106
A7
1006
10
A8
107
A8
8
A8
107
A8
1007
11
A9
108
A9
9
A9
108
A9
1008
12
A10
10
A10
109
A10
1009
13
A11
11
A11
110
A11
1010
14
A12
12
A12
111
A12
1011
15
A13
13
A13
112
A13
1012
16
A14
14
A14
113
A14
1013
17
A15
15
A15
114
A15
1014
Spreadsheet Formulas
Cell
Formula
A3
=VLOOKUP(B3,C3,2,0)
Excel tables to the web >> Excel Jeanie HTML 4
My look Up Formula Is In Cell A3
In Cell B3 The "Lookup Values" It's The Range From "A1:A9"
In Cell C3 The Data List Including Table 1,2,3 "Table Array"
My Results Of Shoul Be In Cells F3:F11
How Can I Apply This
Thanks In Advance
I have subject ID numbers from three waves of data, and I would like the data to align according to ID number so that it looks like this:
1001
1001
1001
1002
1002
1002
1003
1003
1003
1004
1005
1005
1005
1006
1008
1008
1009
1009
1010
1010
1010
1011
1011
1012
1012
1012
But currently, there are no blank spaces if the ID is not present in one of the waves. In other words, it looks like this:
1001
1001
1001
1002
1002
1002
1003
1003
1003
1005
1004
1005
1006
1005
1008
1008
1009
1009
1010
1010
1010
1011
1012
1011
1012
1012
Is there any easy way to do this?
Thanks!
Hi
I have the following input data:
Test1
Test2
Test3
Test4
1001
1010
1005
995
1002
1011
1006
996
1003
1012
1007
997
1004
1013
1008
998
1005
1014
1009
999
1006
1015
1010
1000
I just wanted to have the result like
995
Test4
996
Test4
997
Test4
998
Test4
999
Test4
1000
Test4
1001
Test1, Test4
1002
Test1, Test4
1003
Test1
1004
Test1
1005
Test1, Test3
1006
Test1, Test3
1007
Test3
1008
Test3
1009
Test3
1010
Test2, Test3
1011
Test2
1012
Test2
1013
Test2
1014
Test2
1015
Test2
I think this can be done with PIVOT tables. Please help me in this case.
Thanks in Advance
i have Data in column a and b of the sheet1 like following
Item #
Order in hand
PAK-1000
0
PAK-1001
0
PAK-1002
0
PAK-1003
50
PAK-1004
50
PAK-1005
0
PAK-1006
0
PAK-1007
200
PAK-1008
150
PAK-1009
0
PAK-1010
0
PAK-1011
650
PAK-1012
105
PAK-1013
25
PAK-1014
0
in sheet2 i want that only those items will be shown in the format which i have whose order in hand is grater than 0 like following.
ORDERS PENDING
Picture
Picture
Picture
PAK-1003
PAK-1008
PAK-1013
50
150
25
Picture
Picture
PAK-1004
PAK-1011
50
650
Picture
Picture
PAK-1007
PAK-1012
200
105
is this possible in this format.
Thanks.
Hi,
I must build a hierachy from the excel sheet shown below.
Parent
Child
ChildName
Children
-
1001
Group
Yes
1001
1002
USA Office
Yes
1001
1003
Argentina Office
Yes
1001
1004
Europe
Yes
1004
1005
UK Office
Yes
1004
1006
Spain Office
Yes
1006
1007
Madrid
Yes
1001
1008
Group Management
No
1002
1009
Sales USA
No
1003
1010
Sales Arg
No
1001
1011
Subsidiaries
No
1002
1012
Back-office USA
No
1003
1013
Back-office Arg
No
1005
1014
Back-office UK
No
1007
1015
Back-office Spain
No
1005
1016
Sales UK
No
1007
1017
Sales Spain
No
The aim is to show the hierachy as you can see below.
Level 1
Name 1
Level 2
Name 2
Level 3
Name 3
Level 4
Name 4
Level 5
Name 5
OrgNo
Organisation
1001
Group
1008
Group Management
1001
Group
1002
USA Office
1009
Sales USA
1001
Group
1003
Argentina Office
1010
Sales Arg
1001
Group
1011
Subsidiaries
1001
Group
1002
USA Office
1012
Back-office USA
1001
Group
1003
Argentina Office
1013
Back-office Arg
1001
Group
1004
Europe
1005
UK Office
1014
Back-office UK
1001
Group
1004
Europe
1006
Spain Office
1007
Madrid
1015
Back-office Spain
1001
Group
1004
Europe
1005
UK Office
1016
Sales UK
1001
Group
1004
Europe
1006
Spain Office
1007
Madrid
1017
Sales Spain
I have tried different VBA solution and I think that using scripting dictionary could be one solution. However what should be the logical approach in solving this problem? I tried other solution in VBA, but as there are 30.000 organisational units and the number of levels can be 15, the generation is too slow and it never finished. Could you please help
Hey Guys I Have I Q
I'd Like To Make To Make Every Thing Variable In "Gray Table" So I Can Choose According To My Need I Work
Ex
1- Ranges Variables So I Can Choose The Range I Need
2- Look Up Table Variable So I Can Choose Between Tables I need
3- Color Every Range With Specific Color Through Drop Down List Color
Sheet1
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
1
A1
C 700
Range
Tables
Color
Table1
Table2
Table3
2
A2
C 701
First LookUp
A1
A4
Table1
Blue Sky
A1
C 700
A1
Z 500
A1
Y 300
3
A3
C 702
Second LookUp
A5
A13
Table3
Red
A2
C 701
A2
Z 501
A2
Y 301
4
A4
C 703
Third LookUp
A14
A18
Table2
Yellow
A3
C 702
A3
Z 502
A3
Y 302
5
A5
Y 304
A4
C 703
A4
Z 503
A4
Y 303
6
A6
Y 305
A5
C 704
A5
Z 504
A5
Y 304
7
A7
Y 306
A6
C 705
A6
Z 505
A6
Y 305
8
A8
Y 307
A7
C 706
A7
Z 506
A7
Y 306
9
A9
Y 308
A8
C 707
A8
Z 507
A8
Y 307
10
A10
Y 309
A9
C 708
A9
Z 508
A9
Y 308
11
A11
Y 310
A10
C 709
A10
Z 509
A10
Y 309
12
A12
Y 311
A11
C 710
A11
Z 510
A11
Y 310
13
A13
Y 312
A12
C 711
A12
Z 511
A12
Y 311
14
A14
Z 513
A13
C 712
A13
Z 512
A13
Y 312
15
A15
Z 514
A14
C 713
A14
Z 513
A14
Y 313
16
A16
Z 515
A15
C 714
A15
Z 514
A15
Y 314
17
A17
Z 516
A16
C 715
A16
Z 515
A16
Y 315
18
A18
Z 517
A17
C 716
A17
Z 516
A17
Y 316
19
A18
C 717
A18
Z 517
A18
Y 317
20
A19
C 718
A19
Z 518
A19
Y 318
21
A20
C 719
A20
Z519
A20
Y 319
Spreadsheet Formulas
Cell
Formula
B1
=VLOOKUP(A1,Table1,2,0)
B2
=VLOOKUP(A2,Table1,2,0)
B3
=VLOOKUP(A3,Table1,2,0)
B4
=VLOOKUP(A4,Table1,2,0)
B5
=VLOOKUP(A5,Table3,2,0)
B6
=VLOOKUP(A6,Table3,2,0)
B7
=VLOOKUP(A7,Table3,2,0)
B8
=VLOOKUP(A8,Table3,2,0)
B9
=VLOOKUP(A9,Table3,2,0)
B10
=VLOOKUP(A10,Table3,2,0)
B11
=VLOOKUP(A11,Table3,2,0)
B12
=VLOOKUP(A12,Table3,2,0)
B13
=VLOOKUP(A13,Table3,2,0)
B14
=VLOOKUP(A14,Table2,2,0)
B15
=VLOOKUP(A15,Table2,2,0)
B16
=VLOOKUP(A16,Table2,2,0)
B17
=VLOOKUP(A17,Table2,2,0)
B18
=VLOOKUP(A18,Table2,2,0)
Excel tables to the web >> Excel Jeanie HTML 4
Good day,
I'm not really sure how to explain this,
In "Sheet2" I have a list of numbers, Eg.....
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
On Sheet3 I have data like this.....
AAA111
1000
1002
1004
1005
1007
AAA112
1002
1004
1005
AAA113
1000
1002
1004
1005
1007
1010
BBB111
1001
1002
1003
1004
1005
1006
BBB112
1003
1004
1005
1006
DDD111
1001
1002
1003
1004
1005
1008
DDD112
1002
1004
1005
DDD113
1000
1001
1003
1005
1007
1009
EEE111
1002
1004
1008
1009
FFF112
1000
1001
1002
1003
1004
1005
FFF114
1000
1001
1002
1004
1005
What I would like is a formula so my result on sheet2 looks like this...
1000
AAA111
AAA113
DDD113
FFF112
FFF114
1001
BBB111
DDD111
DDD113
FFF112
FFF114
1002
AAA111
AAA112
AAA113
BBB111
DDD111
DDD112
EEE111
FFF112
FFF114
1003
BBB111
BBB112
DDD111
DDD113
FFF112
1004
AAA111
AAA112
AAA113
BBB111
BBB112
DDD111
DDD112
EEE111
FFF112
FFF114
1005
AAA111
AAA112
AAA113
BBB112
DDD111
DDD112
DDD113
FFF112
FFF114
1006
BBB111
BBB112
1007
AAA111
AAA113
DDD113
1008
BBB111
EEE111
1009
DDD113
EEE111
1010
AAA113
I'm unsure as to go about getting the required results.
Any ideas?
Ak
I need some assistance with an excel problem. I have "a lot" of rows and spreadsheets that are basically made up of the configuration below but many times over. (the below shows two scenarios out of twenty on one tab) I have total sales of $322,455 (the sum of all sales beneath it) for the first one and $80,008 total sales for the other.
I am looking for a formula to determine and designate the VG column by the A through E designations; I did "manually" in the first scenario. The Volume groups (VG) are based upon 20% increments of the total sales. VG "A" then equals 20% of the total 322,455 sales ($0-$64,491), VG "B" the next 20% of the total sales ($64,492-$128,982)...
The problem is that I must keep the "unit" order in numerical order for the entire spreadsheet and cannot sort by rank or anything else without this becoming a completely manual process. So my spreadsheet must stay in numerical "unit" order, yet the summation of sales for the VG designation must begin with the sales of the store ranked "1" first ,then 2... Is there a formula for this? Please help.
322,455
80,008
UNIT
ASST LVL
Sales
Rank
VG
UNIT
ASST LVL
Sales
Rank
VG
1001
5
0
15
E
1001
5
3,895
14
1003
5
26,868
6
C
1003
5
6,807
4
1004
4
24,027
9
D
1004
4
7,600
3
1005
4
30,603
2
A
1005
4
5,490
7
1006
5
29,944
3
B
1006
5
6,007
5
1007
3
29,719
4
B
1007
3
5,039
11
1008
4
34,706
1
A
1008
4
9,668
2
1009
5
17,064
11
E
1009
5
5,476
8
1010
2
14,576
12
E
1010
2
5,213
9
1011
2
17,738
10
E
1011
2
5,115
10
1012
5
25,771
8
D
1012
5
4,331
13
1013
6
25,815
7
D
1013
6
5,926
6
1014
2
9,632
13
E
1014
2
3,203
15
1015
2
7,632
14
E
1015
2
1,615
16
1016
4
28,360
5
C
1016
4
4,623
12
Good day.
I have the following table in sheet3...
data1
data2
data3
data4
data5
1000
XXX
A
X1
RED
1001
BBB
A
X2
GREEN
1002
DDD
A
X3
BLUE
1003
CCC
B
X2
RED
1004
SSS
B
X1
GREEN
1005
WWW
B
X3
BLUE
1006
EEE
A
X3
RED
1007
FFF
B
X1
BLUE
1008
KKK
A
X2
RED
1009
VVV
B
X2
GREEN
1010
RRR
A
X1
GREEN
What I would like to do on sheet2 is get a list of numbers in data1 that matches A in data3. So the result should look like this....
data1
1000
1001
1002
1006
1008
1010
I can then, using this formula in C5 on Sheet2 copied down...
Code:
=IF(B4,VLOOKUP(B4,Sheet3,2,FALSE),"")
Get this result...
data1
data2
1000
XXX
1001
BBB
1002
DDD
1006
EEE
1008
KKK
1010
RRR
So what do I need to put in B4 on sheet2 to get "1000"?
Thanks
Ak
Hi
Kindly see mentioned below table, I want to know that how should a macro split range in col a as per its count, if in each box contains qty 500, as result shown in cols d, e, and f which I did manually by writing Level, qty and box#.
Sheet4
A
B
C
D
E
F
1
Level
Count
Result
2
1001
1234
Level
Qty
Box#
3
1006
910
1001
500
1
4
1007
728
1001
500
2
5
1008
560
1001
234
3
6
1009
718
1006
266
3
7
1010
704
1006
500
4
8
1011
146
1006
144
5
9
1007
356
5
10
1007
372
6
11
1008
128
6
12
1008
432
7
13
1009
68
7
14
1009
500
8
15
1009
150
9
16
1010
350
9
17
1010
354
10
18
1011
146
10
Excel tables to the web >> Excel Jeanie HTML 4
Thanks in advance
I have 150 engineers working across 6 divisions. Each division has different bonus structures. A division may have upto 6 bonus rates. Some engineers may on occasions work across divisions therefore earning bonus in 2 or more divisions. Each division will complete bonus spreadsheets for their division and advise the others divisions if any of their employees have worked in another division for that division to add the bonus info as a miscellaneous employee.
I want to combine the monthly bonus units together so that I send one summary sheet to our head office payroll. In so doing i can check individuals bonus level especially those that are working across divisions.
I am fairly comfortable with v look up - this feels like a 3 dimensional one linking the employee number and the bonus rate heading- is there a formula that does this?
I work with excel 2003.
I've knocked up an example:
BONUS RATE
Employee A B
Division 1 1001 10 10
1002 10 10
1003 10 10
1004 10
MISC: 1007 5
MISC: 1011 10
BONUS RATE
Employee C D
Division 2:1005 20 20
1006 20 20
1007 20 20
1008 20
MISC: 1012 10
1001 10 20
1003 10
BONUS RATE
Employee A B C D E F
Division 3 1009 30 30
1010 30 30
1011 30
1012 30
MISC: 1002 10
1003 10
1008 10
SUMMARY:
BONUS RATE
Employee A B C D E F
Division 1 1001 10 10 10 20
1002 10 10 10
1003 10 10 10 10
1004 10
Division 2 1005 20 20
1006 20 20
1007 5 20 20
1008 20 10
Division 3 1009 30 30
1010 30 30
1011 10 30
1012 10 30
Division 1 Division 2 Division 3
I have searched the forum but not exactly sure what to search for so sorry if this has been asked before (in that case if someone could redirect me I would be grateful). I am trying to perform a fairly simple function which I shall explain (currently I am using VLOOKUPs and then arranging the data)
There are 2 lists, List A and List B of numbers. I want to be able to produce 2 new lists (without gaps/spaces in them- as a result of Vlookup).
Where List C contains numbers from list A that do not appear in List B
Where List D contains numbers from list B that do not appear in List A
A
B
C
D
1001
1001
1004
1014
1002
1002
1011
1015
1004
1005
1017
1005
1006
1006
1008
1008
1009
1009
1013
1011
1014
1013
1015
1017
1018
1018
1019
1019
1020
1020
Many thanks
Rob
Hi
I have developed following macro to filter database but the result I got is containing lof of blanks rows
Private Sub CommandButton1_Click()
Range("Database").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"Criteria"), CopyToRange:=Sheets(" Report").Range("A5:G5"), Unique:=False
End Sub
The result I got like that
A
B
C
D
E
F
G
5
D.ID
P1
P2
P3
P4
P5
P6
6
1001
7
1002
2000
8
1003
9
1004
10
1005
50
11
1006
12
1007
1000
13
1008
2000
14
1009
200
15
1010
16
12
16
1011
17
1012
18
1013
19
1014
20
1015
21
1016
22
1017
23
1018
24
1019
25
1020
26
1021
15
And I have to delete each blank row from row6 which is result total blank from range b5 to g5 and got my mentioned below result
A
B
C
D
E
F
G
D.ID
P1
P2
P3
P4
P5
P6
5
1002
2000
6
1005
50
7
1007
1000
8
1008
2000
9
1009
200
10
1010
16
12
11
1021
15
Could anybody help me to delete said blanks rows with my mentioned above codes?
I am trying to sort a 5 column spreadsheet. I want to sort column A from stores# 1-4000. When I select the worksheet and sort column A. It sorts most of Column A correctly, but not entirely. as an example it sort out like this. The cells are formated as numbers and their all there, just not in order from 1 to 4000.
1
10
100
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
101
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
102
Hi,
Sorry if this has been posted before, first time poster.
I want to convert data from one way to another. Below are the examples:
ID Variable_A Variable_B Variable_C
1001 1
1002 2 5
1003 1 2
1004 2 3
1005 3 5 4
1006 1 2
1007 4
1008 5
1009 2 4
INTO:
ID Var_1 Var_2 Var_3 Var_4 Var_5
1001 1 0 0 0 0
1002 0 1 0 0 1
1003 1 1 0 0 0
1004 0 1 1 0 0
1005 0 0 1 1 1
1006 1 1 0 0 0
1007 0 0 0 1 0
1008 0 0 0 0 1
1009 0 1 0 1 0
If normally use this type of FORMULA:
=IF($B2=1,1,IF($C2=1,1,(IF($D2=1,1,0))))
Where B2 / C2 / D2 are the original data, but will have to copy this formula can change it for VARIABLE 2.
This isn't so much of a problem, until I have about 30 or MORE variables.
Is there anyway that this formula can be done quicker.
Thanks,
Descent73
I've got a worksheet with several tables, each of which I've assigned a defined name. At the top of my worksheet I have several cells containing lookup functions, and these are repeated for each defined table on the sheet.
Right now I'm creating all my lookup functions for a single table, copying the cells containing these functions, and editing the references to the defined names in the copies by hand, giving me a final product something like this:
Code:
A B C D
1
2 10 10 10 10
3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table2,2) =VLOOKUP(C$2,Table3,2) =VLOOKUP(D$2,Table4,2)
4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table2,3) =VLOOKUP(C$2,Table3,3) =VLOOKUP(D$2,Table4,3)
5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table2,4) =VLOOKUP(C$2,Table3,4) =VLOOKUP(D$2,Table4,4)
6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table2,5) =VLOOKUP(C$2,Table3,5) =VLOOKUP(D$2,Table4,5)
To get this result, I'd normally do all the formulas in Column A first, then copy these formulas to Columns B-D, but doing that gives me the result below:
Code:
A B C D
A B C D
1
2 10 10 10 10
3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table1,2) =VLOOKUP(C$2,Table1,2) =VLOOKUP(D$2,Table1,2)
4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table1,3) =VLOOKUP(C$2,Table1,3) =VLOOKUP(D$2,Table1,3)
5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table1,4) =VLOOKUP(C$2,Table1,4) =VLOOKUP(D$2,Table1,4)
6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table1,5) =VLOOKUP(C$2,Table1,5) =VLOOKUP(D$2,Table1,5)
As you can see, I still have to go in to Columns B-D and manually correct the table reference names. So, my question is this: is it possible to enter the text values Table1, Table2, etc. in Row 1, and somehow have all my vlookup functions extract the name of the table to use from these cells? I know I can't just do something like having the formula in A3 =VLOOKUP(A$2,A$1,2) since that will make the formula think that the range A1 itself is where I want to do the lookup. Any ideas?
I need some assistance with an excel problem. I have "a lot" of rows and spreadsheets that are basically made up of the configuration below but many times over . (The below shows two scenarios out of twenty on one tab.) This example also shows only a few of the rows, in reality each example with have 200-882 rows of varying amounts.
I am looking for a formula to determine and designate the VG column by the A through E designations; I did "manually" in the first scenario. The Volume groups (VG) are based upon 20% increments of the total sales. VG "A" then equals 20% of the total 1,216,718 sales ($0-$243,343), VG "B" the next 20% of the total sales ($243,344-$486,687)...
The problem is that I must keep the "unit" order in numerical order for the entire spreadsheet and cannot sort by rank or anything else without this becoming a completely manual process. So my spreadsheet must stay in numerical "unit" order, yet the summation of sales for the VG designation must begin with the sales of the store ranked "1" first ,then 2... Is there a formula for this? Please help. If it matters, I use Excel 2003.
Please let me know how I can change this formula, as I have up to 882 rows (varying for 200-882) for each example. Also some stores will have zero sales, so they will not have a rank.
Scenario #1
Scenario #2
UNIT
Sales
Rank
Description
REGION
VG by $
UNIT
ASST LVL
Sales
Rank
RANK / TTL
REGION
VG by $
1001
63,980
10
North
1
D
1001
5
0
29
100.0%
1
1003
86,953
2
South
2
A
1003
5
26,868
8
27.6%
2
1004
72,416
7
East
3
C
1004
4
24,027
12
41.4%
3
1005
71,218
8
South
2
C
1005
4
30,603
3
10.3%
2
1006
79,654
4
North
1
B
1006
5
29,944
4
13.8%
1
1007
75,453
6
South
2
C
1007
3
29,719
5
17.2%
2
1008
84,716
3
North
1
B
1008
4
34,706
2
6.9%
1
1009
57,717
12
South
2
D
1009
5
17,064
18
62.1%
2
1010
50,716
15
North
1
E
1010
2
14,576
24
82.8%
1
1011
54,899
13
South
2
D
1011
2
17,738
17
58.6%
2
1012
65,364
9
East
3
C
1012
5
25,771
11
37.9%
3
1013
75,519
5
South
2
B
1013
6
25,815
10
34.5%
2
1014
33,612
17
North
1
E
1014
2
9,632
26
89.7%
1
1015
23,390
18
South
2
E
1015
2
7,632
27
93.1%
2
1016
63,500
11
North
1
D
1016
4
28,360
7
24.1%
1
1017
0
South
2
E
1017
4
16,029
22
75.9%
2
1018
157,228
1
East
3
A
1018
5
50,007
1
3.4%
3
1019
49,045
16
South
2
E
1019
4
16,243
20
69.0%
2
1020
51,338
14
North
1
E
1020
3
19,308
16
55.2%
1
1022
2
16,141
21
72.4%
1
1023
2
16,976
19
65.5%
2
1024
6
23,542
13
44.8%
3
1025
2
13,992
25
86.2%
2
1027
3
26,136
9
31.0%
1
1029
4
29,264
6
20.7%
2
1030
4
20,106
15
51.7%
1
1031
1
5,410
28
96.6%
2
1032
3
15,290
23
79.3%
1
1033
4
22,526
14
48.3%
2
Hi All
I have some excel files in the location C:\Excel and these files on said location have to be renamed on very first day of each month that takes lot of time and some time causes to make mistakes. I give you following example as filles name last month
1001 Oct-08
1002 Oct-08
1003 Oct-08
1004 Oct-08
1005 Oct-08
1006 Oct-08
1007 Oct-08
1008 Oct-08
1009 Oct-08
1010 Oct-08
In November very first day I have to rename each files in said location like this
1001 Nov-08
1002 Nov-08
1003 Nov-08
1004 Nov-08
1005 Nov-08
1006 Nov-08
1007 Nov-08
1008 Nov-08
1009 Nov-08
1010 Nov-08
Could it be possible to develop a macro to do the same work.
Thanks in advance
Given the table, First I want to match F2 to range a2:a11, if it matches it will take the amount if there are more than 1 Emp.No. (duplicate emp. no. with different amount) it will sum all the amount. the syntax of the above shall be placed in G2 then drag it...thanks for the help
******** ******************** ************************************************************************>
Microsoft Excel - Book2
___Running: 12.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
G2
=
A
B
C
D
E
F
G
1
Emp. No
Name
Amount
Emp. No.
Amount
2
1003
Mike
100
1001
3
1002
Jake
200
1002
4
1004
Melanie
300
1003
5
1001
John
400
1004
6
1005
Jerry
500
1005
7
1006
Jack
800
1006
8
1007
Jessy
900
1007
9
1008
Jeremy
100
1008
10
1001
John
146
1009
11
1005
Jerry
50
Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Given the table, First I want to match F2 to range a2:a11, if it matches it will take the amount if there are more than 1 Emp.No. (duplicate emp. no. with different amount) it will sum all the amount. the syntax of the above shall be placed in G2 then drag it...thanks for the help
******** ******************** ************************************************************************>
Microsoft Excel - Book2
___Running: 12.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
G2
=
A
B
C
D
E
F
G
1
Emp. No
Name
Amount
Emp. No.
Amount
2
1003
Mike
100
1001
3
1002
Jake
200
1002
4
1004
Melanie
300
1003
5
1001
John
400
1004
6
1005
Jerry
500
1005
7
1006
Jack
800
1006
8
1007
Jessy
900
1007
9
1008
Jeremy
100
1008
10
1001
John
146
1009
11
1005
Jerry
50
Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Given the table, First I want to match F2 to range a2:a11, if it matches it will take the amount if there are more than 1 Emp.No. (duplicate emp. no. with different amount) it will sum all the amount. the syntax of the above shall be placed in G2
******** ******************** ************************************************************************>
Microsoft Excel - Book2
___Running: 12.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
G2
=
A
B
C
D
E
F
G
1
Emp. No
Name
Amount
Emp. No.
Amount
2
1003
Mike
100
1001
3
1002
Jake
200
1002
4
1004
Melanie
300
1003
5
1001
John
400
1004
6
1005
Jerry
500
1005
7
1006
Jack
800
1006
8
1007
Jessy
900
1007
9
1008
Jeremy
100
1008
10
1001
John
146
1009
11
1005
Jerry
50
Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
How to sum the amount of an employee if F1 = to A2:A11 AND G1 = payment code (of C1:C11). NOTE: There might me more than 1 employee with different ...help please
******** ******************** ************************************************************************>
Microsoft Excel - Book2
___Running: 12.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
F1
=
A
B
C
D
E
F
G
H
I
1
Emp. No
Name
Payment Code
Amount
Emp. No.
A1
A2
A3
2
1003
Mike
A1
100
1001
3
1002
Jake
A1
200
1002
4
1004
Melanie
A3
300
1003
5
1001
John
A2
400
1004
6
1005
Jerry
A2
500
1005
7
1006
Jack
A2
800
1006
8
1007
Jessy
A1
900
1007
9
1008
Jeremy
A3
100
1008
10
1001
John
A1
146
1009
11
1005
Jerry
A1
50
Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have a set of data in which I am trying to analyze and filter thru some information and not sure of the best way to get started. I apologize I am pretty good at using excel for formatting, etc. but when it comes to functions I am no expert. Basically I want to flag column A if all of column B for a given column A is CLS. This is just a start to the 50,000 rows of data I am looking thru. So out of the data below I want to somehow "flag" column A where column A equals 1006, 1010 and 1015 because all of their column B values are CLS. If this makes no sense I will try to explain better. Please help me at least get going in the right direction. Thank you!
Col A Col B
1005 ACT
1005 ACT
1005 ACT
1005 ACT
1006 CLS
1006 CLS
1006 CLS
1006 CLS
1006 CLS
1006 CLS
1006 CLS
1007 ACT
1007 ACT
1009 ACT
1009 ACT
1009 ACT
1010 CLS
1011 ACT
1011 ACT
1011 ACT
1013 CLS
1013 ACT
1015 CLS
1015 CLS
1015 CLS
1017 ACT
1017 ACT
1017 ACT
1017 ACT
1017 ACT
1017 ACT
I am in process of creating a macro for my testing activity.
Problem: Sheet1 and Sheet 2 are having set of fields.
Sheet 1 has data as follows:
TC ID
TC NAME
Description
RTID
1001
100
1002
105
1003
110
1004
106
1005
109
1006
108
1007
111
1008
113
1009
115
1010
112
1011
101
1012
103
1013
102
Sheet 2 has data as :
RTID
TCID
Covered
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
Now my macro should update the COVERED Field with GREEN color if it is having TCID - RTID mapping in SHEET1, else mark it with RED.
[IF Sheet2 RTID value is not there in SHEET 1 RTID then mark it as RED.]
Please help me with this.
I am enclosing my spreadsheet for your reference.
Thanks in advance for your kind suggestion and help.
rgds
Parashu
I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or vise-versa.
I've tried the following but got an error stating it was too long:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
Thanks
|
|
