Microsoft Excel - excel problem
Asked By usha anu on 26-Jun-12 07:47 AM
hi ,
i have some data in excel worbook , in 2 worksheet one master sheet and other sheet one .in master sheet i have data like
|
Serial
|
Call ID
|
c opn date
|
c opn time
|
|
0173584
|
DL656050
|
1-Dec-11
|
10:27
|
|
0173584
|
DL655976
|
2-Dec-11
|
14:03
|
and in sheet one data like
|
Serial
|
Date
|
Time
|
|
0173584
|
1-Dec-11
|
10:27
|
i want data in sheet one Like below:
|
Serial
|
Date
|
Time
|
Call id
|
|
0173584
|
1-Dec-11
|
10:27
|
DL656050
|
CAll id take from master sheet against Serial no but according to date and time.
Pichart Y. replied to usha anu on 26-Jun-12 11:44 AM
Hi Usha anu,
I just post the answer for you in another post...
You can do with 2 alternatives, normal formula or array formula..
I show you both ways here.
- Normal formula, seems to be easy but you must create 1 more column to create the Data index. From my sample attachment, it is column E
- input this formula in E2 =A2&C2&D2 then drag the formula down.
- in cell K2 input this formula =INDEX(B2:B7,MATCH(H2&I2&J2,E2:E7,0),1)
- Now about the Array function.
- no need to use the Data index field. We will create it by the Array function...here it is
- in cell K2 input =INDEX($B$2:B7,MATCH(H2&I2&J2,$A$2:A7&$C$2:C7&$D$2:D7,0),1)
- when finish instead of enter, hold ctrl+shift and hit enter
- You will find the { } round your formula..this called Array function.
- like this {=INDEX($B$2:B7,MATCH(H2&I2&J2,$A$2:A7&$C$2:C7&$D$2:D7,0),1)}
- Sample file for you --->ArrFml_IndexArray.zip
Hope this help
Pichart Y.
wally eye replied to usha anu on 26-Jun-12 02:21 PM
Pichart just beat me to it. Here is the formula with your sheet names:
=INDEX(Master!B$2:B$3,MATCH(A2&B2&C2,Master!A$2:A$3&Master!C$2:C$3&Master!D$2:D$3,0))
Just adjust the ranges to match your worksheet. Master column B here contains the Call ID, with the remaining info in A, C and D, Sheet1 has Serial, Date and Time in A, B and C.
usha anu replied to Pichart Y. on 27-Jun-12 06:25 AM
hi ,
thanks but whenever i drag the formula the same value copy and when i double click on cell then it`s show the calculation
Pichart Y. replied to usha anu on 27-Jun-12 12:37 PM
Hi make sure, you set your calculation mode auto calculation
pichart Y.
Microsoft Excel (full name Microsoft Office Excel ) is a spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS
Microsoft Excel (full name Microsoft Office Excel ) is a spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS
end of post Your post shows "end of post" message. Can you post your query again. keywords: Microsoft Excel, Microsoft Excel excel description: excel and word merged end of post 06-Jun-13 10:17 AM
not enough memory to run micorsoft excel. please close other applications and try again. 6 gigs of ram and running 2010 microsoft excel This behavior occurs when you attempt to start any of the programs included in Office on a computer running Microsoft Windows 98, or Windows Millennium Edition (Me) and you have either, turned off virtual memory
Do you want to develop in microsoft office environment? Let say you want to add buttons in microsoft excel, you can use the developer tab to create a vba applications. Developer tab is hidden by default in microsoft office. You can show this tab in less than a minute. Here's how. 1
When i'm on excel on chart i can't make a regular chart but i tryed it only had other than on the lines themselves and select change chart type. Hope this helps. keywords: Microsoft Excel description: Microsoft Excel When i'm on excel on chart i can't make a regular chart
I cannot see Column A in my worksheet. It or any hidden columns. Help. Yes, that's one of the annoying potential problems with Excel. Rather than trying to speculate what might have happened for this problem to occur here it. Good luck. Hi Yes which version you are using? if you are using MS excel 2007 right click on your sheet columns header(on the B column header) then then Check this one explaining the step by step procedure to hide and unhide columns in excel in all versions. . http: / / spreadsheets.about.com / od / excelformatting / qt / 070814unhidecol.htm Suggestion #2 is solve once I knew how! You're a genius! Thank you so much! Gabriella keywords: Microsoft Excel, worksheet, Application, Format, Check description: Microsoft Excel I cannot see Column A in my
end of post end of post keywords: Microsoft Excel, Microsoft Excel networkday description: networkday formula problems end of post 06-Jun-13 10:17 AM
My boss has asked me to become a Microsoft Excel expert by the end of the summer. I would call myself an intermediate level user write a lot of macros and / or formulas. Any help here would be great. Thanks. Excel • Excel 2003 Formulas by John Walkenbach • Excel 2003 Bible by John Walkenbach • Excel Charts by John
I am trying to calculate a formula that calculates threadid = 34530226 However, I need this to be spelled out for me as I am "excel challenged". Hi Jason: assuming that the asset total is located in cell A1 this formula 000015)) Hope this helped, Rolf Thats exactly what I need. Thanks for your help. keywords: Microsoft Excel, COLUMN description: Formula for calculating tiered pricing in Microsoft excel I am trying to calculate a formula that calculates an annual fee based on