Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Vlookup Technique Help

Forum Register
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

Helpful Excel Macros

Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Filter Data to Show the Top 10 Items from a Data Set in Excel - AutoFilter
- This Excel macro filters data in Excel in order to display the top 10 items from the data set. The macro uses the autof
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac

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


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


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


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