Clustering/Classification Using Excel

Feb 21, 2009

I am trying to do clustering/classification using the shortest euclidean distance. In the attached Excel spreadsheet, I am trying to classify new visits in Table 2 into one of the three visits given in Table 1. I have calculated the euclidean distance in Table 3 and classified them into one of the three visits.

The issue I have is that the number of columns in the original data is more than 100 and I don't want to do the euclidean distance for each column (square the difference for each columns and add them) separately. Is there any efficient way to do this classification?

View 9 Replies


ADVERTISEMENT

Delete Records In Set After Classification?

May 23, 2014

I have a large (300K+ records) database with a sizeable amount of duplicate records. I want to delete the duplicates but this is not a matter of simply Remove Duplicates; I need to evaluate them before I do.

I am wondering what functions would:

1) select the specific record in a set of duplicates that makes a determination of a status
2) once the status has been determined for the set, delete all other records

Fields in my database:

ACCIDENT NUM (ID field, in text or General format)
DUP (for Duplicate, indicated by a character, for now its a "?")
OCC_KILLED (in Number format)
OCC_INJURED (in Number format)
SEVERITY (in text format)

Here are some scenarios:

ACC dup K I
12345 ? 0 0
12345 ? 1 2

Or:

ACC dup K I
123456 ? 0 1
123456 ? 1 0

Or:

ACC dup K I
1234567 ? 0 0
1234567 ? 0 2
1234567 ? 0 0

This is the formula for indicating if there are Duplicate records in the larger dataset:

=IF(OR(A2=A3,A2=A1),"?","")

I need to determine the Severity of the accident based on this:

If OCC_KILLED > 0 then SEVERITY = F (for Fatal)
IF OCC_INJURED > 0 and > OCC_KILLED then SEVERITY = I (for Injury)
IF OCC_KILLED and OCC_INJURED >= 0 then SEVERITY = F
IF OCC_KILLED and OCC_INJURED = 0 then SEVERITY = PDO (for Property Damage Only)

I have a code already in place for how to create the value for Severity but it DOES NOT account for duplicate records:

(in SEVERITY field):

=IF(A1<>0, "F",IF(B1<>0,"I","PDO")

sampleset.xls is a sample ot the database.

View 10 Replies View Related

Transferring Data From One Sheet To Another Via Classification?

Jan 15, 2014

I'm not sure that this is possible without VBA, but here goes.. My sample workbook has 3 sheets. 'Master' Sheet A contains fields that are to be completed manually as our sales people talk to prospective customers. Once that information has been input, I'm trying to find a way to have the contact information (columns B - L) to copy automatically to either (in this example) to sheets B or C depending upon the chosen category in Column A, Sheet A. An example is that the sales adviser inputs the information into sheet A and then gives each row of information a category, say B, which then copies the required fields into Sheet B, at the next available row.

View 4 Replies View Related

Count The Number Of People Per Classification Between Two Dates

Apr 21, 2014

Count people per their classification between two dates. Spreadsheet attached.

The answer I need to show in column (S) is:
Cell (S4) = 1 (supervisor)
Cell (S24) = 1 (fitter)
Cell (S36) = 3 (rigger)
j906 count.xlsx

View 1 Replies View Related

Excel 2010 :: How To Save Excel File As Cell Reference Using Macro

Mar 5, 2012

I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)

Sub aaa()
'
' aaa Macro
'

[Code].....

View 1 Replies View Related

Fetching Specific Data From Excel Cell To Different Columns Of Second Excel File?

Sep 7, 2012

I have data in the following format-----

Name : XYZ

City : ABC

Place : sdfg

Error :

price : [X]

cost : [ ]

time : [ ]

[code]....

I want only name, place,desc and under error, i want that type which is marked cross in the brackets(In the above example it is price ) .

name, place,desc,error should be pasted to separate columns in second excel sheet.

View 1 Replies View Related

VBA Open All Text Files In Excel In Folder And Save Them As Excel File

Dec 7, 2013

I have some daily text files in a folder (so about 30 of them each month), which in the end of month, I need to open them up in excel, format them so that I can use the information for my analysis.

I would like to create a macro, to quickly open them all up at once and save them each individually in .xls or .xlsm format.

I am new to VBA and after some research online, I was able to have the files open with the following code. but now I don't know how to proceed further to save them one by one with the same name but in .xls or .xlsm format.

Sub Opentxtfiles()
Dim MyFolder As String
Dim myfile As String

[Code].....

View 2 Replies View Related

Excel 2010 :: Macro To Change Excel Formatted Table Name Dependent On Number Of Loops

Jul 9, 2012

Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).

I am using excel for windows 2010. The macro that i have so far is below.

Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value

[Code] ........

View 1 Replies View Related

Excel 2008 :: Cannot Open Structure Protected Excel Document

Mar 25, 2013

******** type=text/javascript>*********>******** type=text/javascript src="http://pagead2.googlesyndication.com/pagead/show_ads.js">*********>

I tried locking an excel document and i ticked a box that said protect structure, and then entered my password and now I cannot open the document. It is a white square on my desktop now with no options to do anything, it cannot even be attached to an email or deleted. This was on excel 2008 on a macbook.

View 1 Replies View Related

Excel 2013 :: Mail Merging From Excel Database To Outlook

Apr 15, 2014

Using EXcel 2013, Windows 8

I have an Excel worksheet with one column being e-mail addresses. Other columns are Christian names, etc

Ideally can I create a full Mail merge with Outlook using whatever data I want. But probably just e-mail address and Christian name?

Otherwise be able to send one e-mail to all the e-mail addresses, without a major re-type.

View 2 Replies View Related

Excel 2007 :: Create Visio Diagram From Excel?

May 9, 2014

I need to write a code to create visio flow diagram with excel inputs(Excel 2007). Attached is the requirement.

Requirement_Specification.docx

View 1 Replies View Related

Excel 2003 :: Copy Hyperlink From Excel To Outlook?

Apr 19, 2010

I have an Excel 2003 file that contains hyperlinks to OneNote notebooks on a Sharepoint site. An Excel macro looks for these links and determines the full hyperlink address which is then assigned to a variable. An Outlook message is generated which includes the hyperlinks.

The hyperlinks work in the Excel file. I can also copy them from OneNote and manually paste them into an Outlook message and they work. However, when I obtain their full address and transfer that to the email through code, the links do not work. The hyperlink address from OneNote starts with "onenote:http" which is not recognized as a link.

If I can do this manually, there must be a way to do this with vba. Are there characters I need to include in the OneNote hyperlink address to make this work? Is there another way to transfer the working link from Excel to Outlook?

View 3 Replies View Related

Adjust Macro From Excel 2003 To Excel 2010?

Apr 1, 2014

I am trying to adjust the below macro so that it will work in Excel 2010.

Sub OpenAndProcess()
Dim fs As FileSearch
Dim I As Integer

[Code]....

View 3 Replies View Related

Macro To Convert PDF To Excel Data To Excel Format

May 23, 2009

Is there any macro that can be used to convert data which have been converted from PDF to Excel. Currently I'm using a traditional way by pressing F2 and enter to convert the data to excel format. It's tedious ( since the data range is quite big) and crammed my finger.

View 9 Replies View Related

Excel Saved As Zip But Format Is Still Excel?

Jan 11, 2013

I have seen excel zipped but it is visible as excel only. when i double click it it open a folder where same excel document is placed i have to double click it again to open it. how can i save the excel as zip having its same format.

View 1 Replies View Related

Use Name Defined In Excel File To Another Excel File?

Nov 25, 2013

I have defined a name for a data range in an excel file. Now I want to use the same name in different excel file. I tried through various sources but not sure how to do that. Current Soln: I am copying the data from the source file and re-defining the names for the range. But this seems to be duplicate and creates unnecessary space.

View 3 Replies View Related

Using ADO Within Excel?

Jun 21, 2012

I am using ADO to fetch required data from other sheet using sql query. But it is giving me only the headers as a result.

I just want to extract few columns.

This is the code i am using:

Sub example2()
' declare variables
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

[Code]....

View 3 Replies View Related

Excel Function For Hh:mm?

May 23, 2014

see attached excel sheet. On Colum G (highlighted in green) there are hours and minutes. Is there a way I can create a function or macro so that it will convert the values to minutes. For example if the value 1:48 it will convert it 108 minutes or 0:36 will be 36 minutes.

I know I can use =Hour() or =MINUTES() formula but this is long way. My sheet has over 10k data.

Attached file: Book1.xls‎

View 11 Replies View Related

Can't Get The Box In Excel Bigger

May 25, 2014

As you can see in the picture, the box which I am writing in won't get bigger. I want it to cover the whole workscreen, but so far nothing has worked.

View 3 Replies View Related

How To Paste Every Third Row In Excel

Jun 10, 2014

I have 8 columns of data that needs to be alternating every third row by subject data, however there is no option under "paste special." I have been googling and I think I might need a "macro" but I am not sure. The values for each cell are unique and do not repeat.

The format should be like this:

1st row =subject A data
2nd row =subject B data
3rd row =subject C data
4th row =subject A data
ect..

For example how it would look in Excel:

A1 A2 A3 A4 A5 A6 A7 A8
B1 B2 B3 B4 B5 B6 B7 B8
C1 C2 C3 C4 C5 C6 C7 C8
a1 a2 a3 a4 a5 a6 a7 a8

ect.. where Letter# would be a numerical number (such as A1= 101, B2 =93, ect)

There are 45 rows for each subject pool, for a total of 135 rows (which should be alternating by subject data) and there are 8 columns.

View 1 Replies View Related

Excel Notifications

Aug 13, 2014

I have a spreadsheet that contains all of our staff members (around 50 going down the "a" column) and the mandatory training that they must complete (10 subjects going across the top). They are a couple of things that I want to do with this sheet but how to do this.

1) I want the dates in which people's training expires to change colour to blue, those that is about to expire in a month to go green.

2) Copy the names and their training required onto another worksheet so I can keep a list of who I need to arrange training for. Rather than looking down each column and hand writing the names out.is it possible for the sheet to then email me people who's training has expired or is about too?

Example.
Fire safety First aid. Manual handling
John. 12/04/15. 23/08/15. 04/05/15
Frank. 14/05/15. 16/07/15. 08/08/15
Julie. 21/11/15. 11/02/15. 22/10/15
Sharon. 03/04/15. 07/06/15. 12/04/15

View 2 Replies View Related

Excel Or Access?

May 14, 2007

I need a front worksheet with either buttons or tick boxes that will list different options for a machine

Once a tick or push button is activated a hidden block of text related to that specific tick box needs to be selected and placed onto a final print out sheet (allocation)

when futher boxes have been ticked I would like all the information blocks to build up on the final print out sheet.

View 10 Replies View Related

Why Can't See The Cursor In Excel

Mar 3, 2008

I'm talking about the little black box that appears anytime you put the mouse on a cell, or drag the mouse around a group of cells - mine doesn't show up. I can still edit the cell, but I have to guess about which cell I am on.

how to make my dear cursor reappear?

View 1 Replies View Related

Excel Bug ? For SUMIF

Dec 15, 2008

I am writing the sumif function in F2 but it is not working correctly..

View 14 Replies View Related

Wordpad To Excel

Jun 17, 2009

I am hoping someone can help me out of a problem i have. I have some numerical data (temperature readings) that was recorded / stored to a wordpad file.

I have to do some analysis of the data, such as finding mean / maximum and temperature differentials, which i thought would be as easy as opeining up the wordpad file in excel, and then doing formulas (such as =D7-E7).

However, i cannot get any formulas to cork - they all arrive back with VALUE and although all the data is numbers to look at, it might be stored as a text format? (my laymen knowledge isn't that great!)

am i missing out any steps to be able to use the data i have in wordpad as numbers in excel so i can use formulas?

View 14 Replies View Related

Notepad To Excel

Jan 23, 2010

I am wondering how would one go about turning a notepad file into an excel file without all the data being lumped into one cell?

for example the following notepad file is in this format:

20060102 190100;139.14;139.2;139.14;139.15;14
20060102 190200;139.19;139.19;139.16;139.18;16
20060102 190300;139.19;139.2;139.17;139.17;16
20060102 190400;139.16;139.24;139.16;139.2;22
20060102 190500;139.19;139.22;139.19;139.21;7

and I would like it in an excel file in the same format but with each group of digits seperated by a cell:

so for example my first line......

20060102 190100;139.14;139.2;139.14;139.15;14

I would like 20060102 in its own cell, followed by 190100 in its own cell, followed by
;139.14, followed by ;139.15, followed by ;14

View 11 Replies View Related

PDF Table To Excel?

Feb 19, 2013

I have a huge PDF table which I want to export to excel. I tried exporting it using Adobe 10 but all I get is mixedup numbers. Most of the time all the data of one row when exported is only in one cell, which means all the numbers that should appear in individula cells are all place in one cell with spaces. how can I properly export the numbers in the table to excel where each number is in it's own cell.

I have attached a sample of that pdf file.

View 1 Replies View Related

Can't Scroll Down On Excel

May 13, 2013

I'm trying to scroll down my work sheet but it's not letting me. Every time I move the scroll bar down the page it just keeps showing me the same part of the sheet eg cells 1-30 when I want to see all the way down to the bottom. I have some 20,000 lines I need to scroll through but I can't see all of them. To add to this, I keep having lines hide between other lines so theres a big gap like going from line 120 to line 4000 and I have to manually unhide them every time to see them.

View 2 Replies View Related

Excel (Go To) Not Working

Feb 5, 2014

I notice that sometimes the "Go To" function does not work. When I click on the named range Excel just brings the cursor to a the first cell of a worksheet rather than the actual cell/range. I find that I need to restart Windows in order to fix this. A simple restart of Excel does not work.

View 4 Replies View Related

New Tab Within Excel Automatically?

Oct 9, 2013

Is it possible that excel can copy a template tab and create a new tab automatically when requested?

I am trying to create a spread sheet to log all spends within the department but need to be able to generate a front sheet for each purchase order request. I have attached the spread sheet and have highlighted what I am trying to create.

I have never used visual basics so am completely in the dark, can excel do this sort of thing?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved