Excel Extract and Move

[Tokarin]

Hey guys,

I've actually been here leeching abit here and there for the past week.

But this is where problem started.

Everyday i have spreadsheets generated, which gives me raw data like below which starts from row 6 (heading at row 5)

<table border="0" cellpadding="0" cellspacing="0" width="298"><colgroup><col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:4827;width:99pt" width="132"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:77pt" height="20" width="102">Heading1</td> <td class="xl65" style="width:99pt" width="132">Heading2</td> <td class="xl65" style="width:48pt" width="64">Heading3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">MCD123</td> <td class="xl65">A587</td> <td class="xl65">1.1</td> </tr> <tr style="mso-height-source:userset;height:14.25pt" height="19"> <td class="xl65" style="height:14.25pt" height="19">KFC588</td> <td class="xl65">A235</td> <td class="xl65">1.88</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">MCD128</td> <td class="xl65">A578</td> <td class="xl65">1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">ANW887</td> <td class="xl65">A876</td> <td class="xl65">5.58</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">KFC158</td> <td class="xl65">A578</td> <td class="xl65">1.6</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">ANW125</td> <td class="xl65">A215</td> <td class="xl65">1.57</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">KFC782</td> <td class="xl65">A876</td> <td class="xl65">1.75</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">ANW796</td> <td class="xl65">A354</td> <td class="xl65">0.21</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">NAN100</td> <td class="xl65">A578</td> <td class="xl65">5.68</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">MCD786</td> <td class="xl65">A221</td> <td class="xl65">1.5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">NAN873</td> <td class="xl65">A5990</td> <td class="xl65">7.44</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">MCD555</td> <td class="xl65">A575</td> <td class="xl65">1.25</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">NAN123</td> <td class="xl65">A578</td> <td class="xl65">9.14</td> </tr> </tbody></table>


What i need done is actually have a script extract data from heading1 into different sheets with different headings. (Heading1 would always start with MCD, NAN, KFC, ANW BUT the last 3 numbers are random)

I seen a macro with strings, integer and variable that does it.

And would actually like to learn how to do it. what i did now is made it arrange according to heading1 and then have different commands to extract the data.

I would appreciate if i could learn the commands like While, If, variables and all.

For example,

heading1 = 6

While ("A" & heading1 > 0

*If MCD exists, creates new spreadsheet with MCD as name, Cuts and paste whole line (A6 to C6), then records a counter, best if i could include the header from 5:5 in all the other sheets*

if ("A" & heading1 , begins with MCD) then
Range(sheet1,"A" &heading1 & ":" & "C" &heading1).select
Cut to sheet2

it's even better if i could find the first instance where it starts with MCD, and then last instance where it starts with MCD (this works because it's sorted) and then cut and paste all in one shot.

Continues until no MCD exists. Then renames the spreadsheet to MCD & " - " Counter

then continues to ANW, same thing repeats itself. Just need some guidance for at least MCD, and i think i'll get the flow for the rest.


I really appreciate all the help. =) Anyways, the above data is jz an example, Data actually goes up to 500 rows from column A to Column BB.

So... yeah. =)

Thanks,
Tokarin =)

 

[Mindpsyche]

I'm still trying to figure out what you need.

So you want to group all the MCD's into one sheet and the others depending on their first 3 letters into respective sheets?

 

[Tokarin]

yeah, that's the ultimate goal with a count of how many there are in that sheet in the name of that sheet

 

[Mindpsyche]

So this is what i thought off:

First enter a column with LEFT("Cell with name",3) - To extract the first 3 letters

Then using advanced filters, showing unique records only you can arrive at how many unique 3 letter names you have. This will give you the number of sheets you need to insert. Also, you could name each sheet from the same range where you extracted the unique records.

To get a count of how many MCD's, KFC's or otherwise are present you can do a countif with in the same column where you entered your left formula.

Excel 2007
	A	B	C	D	E	F	G	H
4						Name of Worksheets	Frequency
5	Heading1	Heading2	Heading3		LEN	UNIQUE RECORDS	COUNT IF	Worksheet name
6	MCD128	A587	1.1	MCD	MCD	MCD	2	MCD -2
7	KFC588	A630	1.9	KFC	KFC	KFC	2	KFC -2
8	MCD128	A950	1.32	MCD	MCD	ANW	2	ANW -2
9	ANW887	A546	9.45	ANW	ANW	NAN	1	NAN -1
10	KFC158	A900	3.56	KFC	KFC
11	NAN203	A368	2.31	NAN	NAN
12	ANW976	A403	2.4	ANW	ANW
Data input

Cell Formulas
Range	Formula
D6	=LEFT(A6,3)
D7	=LEFT(A7,3)
D8	=LEFT(A8,3)
D9	=LEFT(A9,3)
D10	=LEFT(A10,3)
D11	=LEFT(A11,3)
D12	=LEFT(A12,3)
E6	=LEFT(A6,3)
E7	=LEFT(A7,3)
E8	=LEFT(A8,3)
E9	=LEFT(A9,3)
E10	=LEFT(A10,3)
E11	=LEFT(A11,3)
E12	=LEFT(A12,3)
G6	=COUNTIF($E$6:$E$12,F6)
G7	=COUNTIF($E$6:$E$12,F7)
G8	=COUNTIF($E$6:$E$12,F8)
G9	=COUNTIF($E$6:$E$12,F9)
H6	=F6&" "&"-"&G6
H7	=F7&" "&"-"&G7
H8	=F8&" "&"-"&G8
H9	=F9&" "&"-"&G9

Try recording the macro and playing around with the first part. The code for copy pasting the rows would not be too dificult although I myself would not be able to help with it...

Hope this helps

 

[Tokarin]

thanks for that idea.

But i am actually more interested in a Vbscript kinda macro that allows 100% automation of extracting without adding formulas to the spreadsheet.

I was actually thinking of having a script run and check for the first row where A starts with MCD and then continue to check for the last row where A starts with MCD, then data is extracted from "A"& startnumber &:&"BB"&Endnumber" to spreadsheet MCD