Combine rows of data and use if/then with results

Greetings,
I am trying to combine rows of data for e-learning students and then 
determine certification status; here’s an extract as an example:

Smith	Module 1	Completed
Jones	Module 1	Completed
Doe	Module 1	Not Started
Smith	Module 2	Completed
Jones	Module 2	Completed
Doe	Module 2	Not Started
Smith	Module 3	Completed
Jones	Module 3	In Progress
Doe	Module 3	Not Started

First I’d like to sort by Last Name.   Once sorted I need status – if all 3 
modules completed, status='certified'.  If only 1 or 2 have been completed, 
status='in progress', if none have been started, status='not started'.  
Ultimate goal is:
Smith   Completed
Jones   In Progress
Doe      Not Started
File will be new each month (download from another system) and number of 
rows will vary.
Many thanks for your help!
0
Utf
12/4/2009 4:31:02 PM
excel.programming 6508 articles. 1 followers. Follow

6 Replies
574 Views

Similar Articles

[PageSpeed] 7

One way. Assumes 3 columns
Sub getstudentstatus()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:C" & lr).Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
On Error Resume Next
For i = lr To 2 Step -3
Cells(i, 4) = "In Progress"
If Application.CountIf(Range(Cells(i - 2, 3), _
Cells(i, 3)), "Completed") = 3 Then Cells(i, 4) = "Completed"
If Application.CountIf(Range(Cells(i - 2, 3), _
Cells(i, 3)), "Not Started") = 3 Then Cells(i, 4) = "Not Started"
Next i
With Range("A1:D1")
..AutoFilter
..AutoFilter Field:=4, Criteria1:="<>"
End With
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"CJOHNSO92" <CJOHNSO92@discussions.microsoft.com> wrote in message 
news:CB62E36C-3F1D-422B-8D48-917165643C04@microsoft.com...
> Greetings,
> I am trying to combine rows of data for e-learning students and then
> determine certification status; here’s an extract as an example:
>
> Smith Module 1 Completed
> Jones Module 1 Completed
> Doe Module 1 Not Started
> Smith Module 2 Completed
> Jones Module 2 Completed
> Doe Module 2 Not Started
> Smith Module 3 Completed
> Jones Module 3 In Progress
> Doe Module 3 Not Started
>
> First I’d like to sort by Last Name.   Once sorted I need status – if 
> all 3
> modules completed, status='certified'.  If only 1 or 2 have been 
> completed,
> status='in progress', if none have been started, status='not started'.
> Ultimate goal is:
> Smith   Completed
> Jones   In Progress
> Doe      Not Started
> File will be new each month (download from another system) and number of
> rows will vary.
> Many thanks for your help! 

0
Don
12/4/2009 5:49:36 PM
A 'no code' solution:
If the Status establishing algorithm is as you describe, then you can
use this formula (enter in the first row and fill down):

=3DCHOOSE(IF(SUMPRODUCT((($A$1:$A$9)=3DA1)*($C$1:$C$9=3D"Not Started"))
=3D3;1;SUMPRODUCT((($A$1:$A$9)=3DA1)*($C$1:$C$9=3D"Completed"))+1);"Not
Started";"In Progress";"In Progress";"Certified")

This assumes that your data are in range A1:A9. Also - i'm using ';'
as list separator - in case you use comma ',' - then you need to
replace my ';' with ','.

This would mark all the individuals (even without sorting the list)
based on this logic:
- if all 3 modules for the last name are 'Not Started', then marked as
'Not Started'
- if all 3 modules for the last name are 'Completed', then marked as
'Certified'
- all the rest would be marked as 'In Progress'

Then, if you need to see every indivudual only once - you can filter
out the Unique values by using advanced filter.

A.



On 4 Dec, 16:31, CJOHNSO92 <CJOHNS...@discussions.microsoft.com>
wrote:
> Greetings,
> I am trying to combine rows of data for e-learning students and then
> determine certification status; here=92s an extract as an example:
>
> Smith =A0 Module 1 =A0 =A0 =A0 =A0Completed
> Jones =A0 Module 1 =A0 =A0 =A0 =A0Completed
> Doe =A0 =A0 Module 1 =A0 =A0 =A0 =A0Not Started
> Smith =A0 Module 2 =A0 =A0 =A0 =A0Completed
> Jones =A0 Module 2 =A0 =A0 =A0 =A0Completed
> Doe =A0 =A0 Module 2 =A0 =A0 =A0 =A0Not Started
> Smith =A0 Module 3 =A0 =A0 =A0 =A0Completed
> Jones =A0 Module 3 =A0 =A0 =A0 =A0In Progress
> Doe =A0 =A0 Module 3 =A0 =A0 =A0 =A0Not Started
>
> First I=92d like to sort by Last Name. =A0 Once sorted I need status =96 =
if all 3
> modules completed, status=3D'certified'. =A0If only 1 or 2 have been comp=
leted,
> status=3D'in progress', if none have been started, status=3D'not started'=
.. =A0
> Ultimate goal is:
> Smith =A0 Completed
> Jones =A0 In Progress
> Doe =A0 =A0 =A0Not Started
> File will be new each month (download from another system) and number of
> rows will vary.
> Many thanks for your help!

0
AB
12/4/2009 5:50:53 PM
Hi Don,
Thanks.  getting a compile error at AutoFilter, not sure why.  Also, would 
appreciate your recommendation on best way to get the macro into the file 
each month...maybe have a template file with the macro and copy the data in?  
I won't be the one running this monthly, so trying to make as easy as 
possible for the person who will be.
Thanks,
Carol

"Don Guillett" wrote:

> One way. Assumes 3 columns
> Sub getstudentstatus()
> lr = Cells(Rows.Count, 1).End(xlUp).Row
> Range("A1:C" & lr).Sort Key1:=Range("A2"), Order1:=xlAscending, _
> Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
> Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
> On Error Resume Next
> For i = lr To 2 Step -3
> Cells(i, 4) = "In Progress"
> If Application.CountIf(Range(Cells(i - 2, 3), _
> Cells(i, 3)), "Completed") = 3 Then Cells(i, 4) = "Completed"
> If Application.CountIf(Range(Cells(i - 2, 3), _
> Cells(i, 3)), "Not Started") = 3 Then Cells(i, 4) = "Not Started"
> Next i
> With Range("A1:D1")
> ..AutoFilter
> ..AutoFilter Field:=4, Criteria1:="<>"
> End With
> End Sub
> 
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "CJOHNSO92" <CJOHNSO92@discussions.microsoft.com> wrote in message 
> news:CB62E36C-3F1D-422B-8D48-917165643C04@microsoft.com...
> > Greetings,
> > I am trying to combine rows of data for e-learning students and then
> > determine certification status; here’s an extract as an example:
> >
> > Smith Module 1 Completed
> > Jones Module 1 Completed
> > Doe Module 1 Not Started
> > Smith Module 2 Completed
> > Jones Module 2 Completed
> > Doe Module 2 Not Started
> > Smith Module 3 Completed
> > Jones Module 3 In Progress
> > Doe Module 3 Not Started
> >
> > First I’d like to sort by Last Name.   Once sorted I need status – if 
> > all 3
> > modules completed, status='certified'.  If only 1 or 2 have been 
> > completed,
> > status='in progress', if none have been started, status='not started'.
> > Ultimate goal is:
> > Smith   Completed
> > Jones   In Progress
> > Doe      Not Started
> > File will be new each month (download from another system) and number of
> > rows will vary.
> > Many thanks for your help! 
> 
> .
> 
0
Utf
12/4/2009 8:37:01 PM
Thanks, AB.  trying this next

"AB" wrote:

> A 'no code' solution:
> If the Status establishing algorithm is as you describe, then you can
> use this formula (enter in the first row and fill down):
> 
> =CHOOSE(IF(SUMPRODUCT((($A$1:$A$9)=A1)*($C$1:$C$9="Not Started"))
> =3;1;SUMPRODUCT((($A$1:$A$9)=A1)*($C$1:$C$9="Completed"))+1);"Not
> Started";"In Progress";"In Progress";"Certified")
> 
> This assumes that your data are in range A1:A9. Also - i'm using ';'
> as list separator - in case you use comma ',' - then you need to
> replace my ';' with ','.
> 
> This would mark all the individuals (even without sorting the list)
> based on this logic:
> - if all 3 modules for the last name are 'Not Started', then marked as
> 'Not Started'
> - if all 3 modules for the last name are 'Completed', then marked as
> 'Certified'
> - all the rest would be marked as 'In Progress'
> 
> Then, if you need to see every indivudual only once - you can filter
> out the Unique values by using advanced filter.
> 
> A.
> 
> 
> 
> On 4 Dec, 16:31, CJOHNSO92 <CJOHNS...@discussions.microsoft.com>
> wrote:
> > Greetings,
> > I am trying to combine rows of data for e-learning students and then
> > determine certification status; here’s an extract as an example:
> >
> > Smith   Module 1        Completed
> > Jones   Module 1        Completed
> > Doe     Module 1        Not Started
> > Smith   Module 2        Completed
> > Jones   Module 2        Completed
> > Doe     Module 2        Not Started
> > Smith   Module 3        Completed
> > Jones   Module 3        In Progress
> > Doe     Module 3        Not Started
> >
> > First I’d like to sort by Last Name.   Once sorted I need status – if all 3
> > modules completed, status='certified'.  If only 1 or 2 have been completed,
> > status='in progress', if none have been started, status='not started'..  
> > Ultimate goal is:
> > Smith   Completed
> > Jones   In Progress
> > Doe      Not Started
> > File will be new each month (download from another system) and number of
> > rows will vary.
> > Many thanks for your help!
> 
> .
> 
0
Utf
12/4/2009 8:43:01 PM
      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"CJOHNSO92" <CJOHNSO92@discussions.microsoft.com> wrote in message 
news:6D9D9184-F671-4D51-B1C1-D81556C53DBE@microsoft.com...
> Hi Don,
> Thanks.  getting a compile error at AutoFilter, not sure why.  Also, would
> appreciate your recommendation on best way to get the macro into the file
> each month...maybe have a template file with the macro and copy the data 
> in?
> I won't be the one running this monthly, so trying to make as easy as
> possible for the person who will be.
> Thanks,
> Carol
>
> "Don Guillett" wrote:
>
>> One way. Assumes 3 columns
>> Sub getstudentstatus()
>> lr = Cells(Rows.Count, 1).End(xlUp).Row
>> Range("A1:C" & lr).Sort Key1:=Range("A2"), Order1:=xlAscending, _
>> Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
>> Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
>> On Error Resume Next
>> For i = lr To 2 Step -3
>> Cells(i, 4) = "In Progress"
>> If Application.CountIf(Range(Cells(i - 2, 3), _
>> Cells(i, 3)), "Completed") = 3 Then Cells(i, 4) = "Completed"
>> If Application.CountIf(Range(Cells(i - 2, 3), _
>> Cells(i, 3)), "Not Started") = 3 Then Cells(i, 4) = "Not Started"
>> Next i
>> With Range("A1:D1")
>> ..AutoFilter
>> ..AutoFilter Field:=4, Criteria1:="<>"
>> End With
>> End Sub
>>
>> -- 
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "CJOHNSO92" <CJOHNSO92@discussions.microsoft.com> wrote in message
>> news:CB62E36C-3F1D-422B-8D48-917165643C04@microsoft.com...
>> > Greetings,
>> > I am trying to combine rows of data for e-learning students and then
>> > determine certification status; here’s an extract as an example:
>> >
>> > Smith Module 1 Completed
>> > Jones Module 1 Completed
>> > Doe Module 1 Not Started
>> > Smith Module 2 Completed
>> > Jones Module 2 Completed
>> > Doe Module 2 Not Started
>> > Smith Module 3 Completed
>> > Jones Module 3 In Progress
>> > Doe Module 3 Not Started
>> >
>> > First I’d like to sort by Last Name.   Once sorted I need status 
>> > – if
>> > all 3
>> > modules completed, status='certified'.  If only 1 or 2 have been
>> > completed,
>> > status='in progress', if none have been started, status='not started'.
>> > Ultimate goal is:
>> > Smith   Completed
>> > Jones   In Progress
>> > Doe      Not Started
>> > File will be new each month (download from another system) and number 
>> > of
>> > rows will vary.
>> > Many thanks for your help!
>>
>> .
>> 

0
Don
12/4/2009 9:04:14 PM
This code of Don Guillett (has 2 dots):

With Range("A1:D1")
...AutoFilter
...AutoFilter Field:=4, Criteria1:="<>"
End With

Should be changed to (1 dot):

With Range("A1:D1")
  .AutoFilter
  .AutoFilter Field:=4, Criteria1:="<>"
End With



hth,
-- 
Data Hog


"CJOHNSO92" wrote:

> Hi Don,
> Thanks.  getting a compile error at AutoFilter, not sure why.  Also, would 
> appreciate your recommendation on best way to get the macro into the file 
> each month...maybe have a template file with the macro and copy the data in?  
> I won't be the one running this monthly, so trying to make as easy as 
> possible for the person who will be.
> Thanks,
> Carol
> 

0
Utf
12/7/2009 1:11:01 AM
Reply:

Similar Artilces:

How can I create a chart to compare multiple data series?
Using Excel, is there a way to merge multiple charts with different data series into one to demonstrate a direct comparison of the data series within one single chart. I've got 4 charts of the stacked column type. magnoliak77 Tufte calls them small multiples, Cleveland calls them trellis displays. I call them panel charts. Take a look at the examples on this page. http://processtrends.com/toc_panel_charts.htm Another option is to stick with your 4 charts, however, you can size and align them. Here's a link to a simple macro that sizes and aligns all charts on a single sheet....

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

to use workday function in excel vba code
hi all, is there a way to use wrokday function in my worksheet controls.? i have a date time picker in my worksheet and a text box and a button. on click of this button i should get next desired date. like when i select a date from the calendar and click on button ther is onclick function in my macro. this onclick should calculate next desired date(assume if i choos 10/11/2005 and add 2 to this date i should get 12/11/2005) i know how to use workday function using a cell reference but................ i don't know how to put this into a vba code. if i use workday("10/11/2005&qu...

Restart numbering ater a set number of rows
I have an excel spreadsheet with 4 columns. the first two columns contains numbers which will never change the third column starts off with 0 (zero) and is repeated for 255 rows then on the 256 row it needs to change to 1 (so basically increment by 1 every 255 rows) the fourth column starts at 0 (zero) and counts up to 255 but then needs to reset it self back to 0 on the 256 row. Help Please!!!!!:confused: ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way: I...

Row Number
I need hellp with a formula. In Cell E8 I need to write a formula that will search Column A rows 18 thru 32 and if it finds the date 09/16/2006 it will display the row number. Any help will be greatly appreciated. Thanks Dianne You didn't say what result you want if the date is not present. One way... A1 = some date =IF(COUNTIF(A18:A32,A1),INDEX(ROW(A18:A32),MATCH(A1,A18:A32,0)),"") If the date isn't present the formula returns blank. -- Biff Microsoft Excel MVP "Dianne" <Dianne@discussions.microsoft.com> wrote in message n...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

Vlookup in vba
Hi I have the following snippet of code: Sheets("mdata").Select Range("E1").Select ActiveCell.FormulaR1C1 = "RVU" Range("E2").Select ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2)),0,VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2))" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E" & LastRow), Type:=xlFillDefault This fills about 5,000 rows with look up data from the rvu ws, and puts in 0 if #N/A is obtained. HOWEVER, if I was ...

import excel spreadsheet without empty rows (causing nulls)
Hi. I am trying to import an excel file into access. The import goes fine or so I thought. When I opened the table it had all my excel data plus over 9000 null valued records.So instead of getting a record set number of 200 records, I got over 9200. I have re imported the table after deleting empty rows (<-it felt pointless because there was nothing that I was deleting, I just figured there was something hidden) in the spreadsheet but it did the same thing. Is there something that attaches to the spreadsheet that causes null records? Please let me know if you know what has happened. Thank...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

One use licence
I have purchased a one use student/teacher copy of Microsoft office 2007 from my colleges IT department. My laptop has recently developed a serious fault and will need to be replaced. I still have the disks and I was wondering, if i remove the program from my old laptop will i be able to install it on my new machine. If so what would I need to do? On 2/28/2010 8:05 AM, Mr Random101 wrote: > I have purchased a one use student/teacher copy of Microsoft office 2007 from > my colleges IT department. My laptop has recently developed a serious fault > and will need to be...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

transfer of data
Is there a way to transfer data that is in outlook to an exce spreadsheet. I have name, address, city, state, zip, all phone numbers that I woul like to send to certain cells in a spreadsheet. If this is possible, what code could I write help me do this? :confused: Thank You Michae -- Message posted from http://www.ExcelForum.com Hi in Outlook you can save your data as *.csv file. Open this exported file in Excel -- Regards Frank Kabel Frankfurt, Germany "daniels012 >" <<daniels012.1byt7v@excelforum-nospam.com> schrieb im Newsbeitrag news:daniels012.1byt7v@excel...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

Can I format an entire row based on the value of a single cell?
I would like to use the conditional formatting feature to format an entire row rather than just a single cell. For example: If the cell value = "Total" then the entire row is bold. select your range (multiple rows???). Then with A3 (say) the activecell in that selection: format|Conditional formatting formula is: =$a3="total" apply a nice format LTShelley wrote: > > I would like to use the conditional formatting feature to format an entire > row rather than just a single cell. For example: If the cell value = "Total" > then the entire row is bo...

unpleasant sounds when using Cut & Paste
I'm running Excel 2000 under XP Home, all Office patches & updates applied (9.0.6926 SP-3). When I cut, copy and/or paste (using edit or right click menu), I hear a very unpleasant sound for each operation, something like "boin.. ggg" for cut/copy and a high pitched "swoosh" for paste. *Occasionally*, I get a normal copy sound, never a normal paste sound. This does not happen in Word, nor in any other application where the same editing operations are performed. - I've had to mute all sounds in self-defense, but this isn't a satisfactory solution. M...

how to check short cuts using $env
Hello Mates, need your quick assistance on this simple problem. I wish to check shortcut under start menu. I'm specifically looking for bunch of them. e.g. let's take one C:\Documents and Settings\pzare_test\Start Menu \Programs\TextPad if use this as variable e.g. "$Env:USERPROFILE\start Menu\Programs \TextPad.lnk" it works $link="$Env:USERPROFILE\start Menu\Programs\TextPad.lnk" test-path $link ------------------------ But if use something like this (as i want to check number of shortcuts) $links=get-content linkfile.txt foreach($link in $link...

CDATA, XmlTextWriter, Read data
Hi All, I am using XmlTextWriter to write CDATA section using following code: XmlTextWriter xtw = new XmlTextWriter(Console.Out); xtw.WriteStartElement("data"); xtw.WriteCData("UNSAFE SCRIPT DATA CONTAINING & and < WHICH GETS CONVERTED TO &amp; and &lt;. SO HAVE TO USE WriteCData() METHOD HERE"); xtw.WriteEndElement(); xtw.Flush(); xtw.Close(); I suppose the data gets written to xtw instance. Now I want to retrieve the data back. How should I do that? Please let me know ASAP. Thanks, Gem Pramod wrote: > Hi All, > > I am using XmlTextWriter to ...

How to Combine Data in Different Columns
Hi Everyone I need help in this issue. I have tried many ways though some wer successful but they were not efficient. I have say 5 columns of data and the data could be on the same rows o all 5 columns or on different rows hence if I need to combine all th data of these 5 columns, I would have to sort or filter each of the manually and copy and paste to a different worksheet or column. This i very tedious and time consuming. I also tried to write a macro to loo for data in one column then put them into another column but i was no successful. However, experts like you guys shouldn't have ...

Splitting Rows
Hello, I have a rather large Excel sheet in which Row 1 contains a Name and Row 2 the Address. This continues (Odd rows having name, even address) throughout the entire sheet. All the information is under column A. Is there a way to split the information so that the odd rows stay in column A and the even rows move to Column B? Thank you in advance, Jack Jack Put this formula in B1 and copy down. =IF(MOD(ROW(A1),2)=1,OFFSET(A1,1,0),"") Copy the formulae in column B and edit>paste special...>values back over itself to 'kill' the formulae You can then filter t...

Move historical data
Hi, I have sql2008 enterprise edition. Every day at night I would like to move all data, older than 1 year, from my operative database to other, read only historical database, because users do updates, deletes and inserts only on data not older than 1 year. If user in my program looks for data older than one year(that is maybe case in 5%), I change connection in background to read only historical database and disable all update/delete/insert buttons on user interface. So, i have job, which executes every night. It change historical database to not read only, insert data...

Edit table data automatically, with InStr
Okay I have my data being automatically imported to a table (TableNew). One of my fields is Called "File Name." For the new data imported, the layout of the information in this field is example: John.Pictures or Maryam.Videos. After the data is imported, I want the code to go through the data in this field and remove anything after the decimal point, including the decimal point. So the "File Name" for the examples above should be "John" or "Maryam". I know this involves the InStr however I am not sure how exactly to use it. Could someone provide the corr...

External Data Query, named table not available for query in copies
In Excel 2000 I have extracted data from one workbook into another using the Data > Get External Data, Databse > Excel functionality. I established a query and the desired data appears. I require multiple source/template workbooks from which I plan to run the queries, but when I copied the original source sheet and try to use the Data > Get External Data, Databse > Excel method I used previously I get an error indicating no table is defined. The table range is named the same as the original file, but Excel just doesn't seem to acknowledge it is there. Newly created test she...

How To Delete the Empty Rows
X .. .. Y .. .. .. Z .. .. .. A .. .. B Dear Sir or Madam Could you show me how to delete the empty rows and keep the colum in the same order(As below)using the Excel Function? X Y Z A B Thanks for your help! If the cells are really empty, you could: Select the column Edit|goto (in xl2003 menus) or F5 or ctrl-g Click Special Then Blanks Then rightclick on one of the selected cells and choose Delete (and entire row--I think that's what you want). On 09/02/2010 16:27, Han wrote: > X > . > . > Y > . > . > . > Z > . > . > . > A > . > . > B &g...

one data series
i am trying to create a graph that tracks price/metric ton over time. on a second y axis i would like to have the equivalent price per lb. i only want to see one line on the graph, but whenever i try to do this, i come up with two lines. any ideas? thanks, matt Matt - You need at least one series per set of axes. But the second series can be formatted so it is invisible (no markers and lines, or no border and no fill). - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ matt wrote: > i am trying to create a graph that t...

Data Validation #34
Hi, is it possible to programme an option in a validation list so that when the option, say "print", is selected the last row of data entered on a worksheet is copied and pasted into the next available row in another worksheet. For example, when the "print" option is selected from the drop down menu , all the data on cells A5:P5 are selected (as this is the last info enterd on this worksheet) and then paste it into cells A45:P45 in another worksheet as this is the next available row ! Hope I have made myself clear and thanks in advance I think I'd use a dedicate...