Want to Automatically Re-Categorize a Downloaded Internet Spreadsheet

[The Moose]

There is an internet spreadsheet that I pull into a local spreadsheet
to reconfigure for my own use.

Their categories are defined like:
OtherAnimals,FarmKitchenware,cows,castiron
NEWoct2nd_7th,deer

I would like to be able to pick out the first word before the comma
(unless the first word contains 'new', then I want the second word) --
then I want to populate with my own categories.

If there is a 'special word' like "castiron", I want that category to
override all others. I have about 6 or 7 "special words" that should
override whatever is in their category field.

There are about two thousand items on their spreadsheet and they have
to be pushed into my 388 categories.

I've got a list of "MY" categories on a separate sheet in my workbook.
I've been sorting on their categories and then cutting and pasting
which works fine for the first time. But, their spreadsheet updates
sometimes daily, sometimes several times a week -- which would mean
that every time I pull down an updated version of their spreadsheet, I
would have to re-categorize.

I have no control over the format of the internet spreadsheet --
different company.

Thanks for any suggestions.

Barb

 

[The Moose]

Well, it would have killed ten ordinary people -- but, I finally got it
done.

(1) I have a separate worksheet that has two columns -- the first
column has the categories as listed on the internet spreadsheet. The
second column has the categories that I want to push the data into.
The columns match up -- category in column one points to appropriate
category in column two. Both columns are sorted as a unit based on
column one.

(2) I created a column on my main spreadsheet called 'extracted
categories'. The is the formula:
=IF(LEN(M2)=0,"",IF(ISERR(FIND(",",M2)),M2,LEFT(M2,SEARCH(",",M2,1)-1)))
(This formula pulls out the first word before the comma out a data-set
like this: Banks,pigs,OtherAnimals,castiron. If there is only one
word, it enters that one word.)

(3) I created another column entitled "Categories" which contains this
formula:
=IF(I2>"NEW*","New Arrivals",LOOKUP(I2,Categories!A:A,Categories!B:B))
(This formula uses the two columns created in #1 above and matches
based on the value in column one and enters the value from column two
into my "Categories" column. The NEW* looks for entries like this:
NEWoct2nd_7th,TinSigns
and pushes the data into the category called "New Arrivals". One the
'newness' wears off, this data will be re-categorized by the owner of
the internet spreadsheet.

I pull down a spreadsheet from the internet using this formula (both
spreadsheets have to be open) for this to work:
Sub LIVEIWDSC()
ActiveWorkbook.Names.Add Name:="web", RefersToR1C1:= _
"='URL[name_of_workbook.xls]name_of_spreadsheet'!R2C1:R2000C10"
Sheets("Sheet2").Range("a2:j2000").Formula = "=web"
MyWait 1
Sheets("Sheet1").Range("k2:t2000").Value =
Sheets("Sheet2").Range("a2:j2000").Value
Sheets("Sheet2").Range("a2:j2000").Clear
ActiveWorkbook.Names("web").Delete
End Sub
Sub MyWait(PauseSeg As Double)
' Orlando Magalhães Filho
Dim Start
Start = Timer
Do While Timer < Start + PauseSeg
DoEvents
Loop
End Sub
(This pulls down about 2000 rows of data by 10 columns wide and places
it starting in column K/row 2.

I have a column that checks for duplicates, like so, because the data
they supply me with always has duplicates.:
=IF(MAX(COUNTIF(range1,range1))>1,"Duplicates","No Duplicates")

I have either formulas or data in the first 10 columns of my
spreadsheet that get populated when I pull down a new version of the
internet spreadsheet.

All I have to do is capitalize the product names, spellcheck the
product names, and remove the double-quotes from the product names
because they don't convert 'nicely' when I convert the entire
spreadsheet to a TAB file.

There ya' go.

I've been given a lot of help by a lot of different people in this
forum. I would not have been able to accomplish this without their
help. I want to thank everyone who has helped me with this.

Barb

 

[The Moose]

Well, it would have killed ten ordinary people -- but, I finally got it

done.

(1) I have a separate worksheet that has two columns -- the first
column has the categories as listed on the internet spreadsheet. The
second column has the categories that I want to push the data into.
The columns match up -- category in column one points to appropriate
category in column two. Both columns are sorted as a unit based on
column one.


(2) I created a column on my main spreadsheet called 'extracted
categories'. The is the formula:
=IF(LEN(M2)=0,"",IF(ISERR(FIND(",",M2)),M2,LEFT(M2,SEARCH(",",M2,1)-1)))

(This formula pulls out the first word before the comma out a data-set
like this: Banks,pigs,OtherAnimals,castiron. If there is only one
word, it enters that one word.)


(3) I created another column entitled "Categories" which contains this
formula:
=IF(I2="NEW*","New Arrivals",LOOKUP(I2,Categories!A:A,Categories!B:B))
(This formula uses the two columns created in #1 above and matches
based on the value in column one and enters the value from column two
into my "Categories" column. The NEW* looks for entries like this:
NEWoct2nd_7th,TinSigns
and pushes the data into the category called "New Arrivals". One the
'newness' wears off, this data will be re-categorized by the owner of
the internet spreadsheet.


I pull down a spreadsheet from the internet using this formula (both
spreadsheets have to be open) for this to work:
Sub LIVEIWDSC()
ActiveWorkbook.Names.Add Name:="web", RefersToR1C1:= _
"='URL[name_of_workbook.xls]name_of_spreadsheet'!R2C1:R2000C10"

Sheets("Sheet2").Range("a2:j2000").Formula = "=web"
MyWait 1
Sheets("Sheet1").Range("k2:t2000").Value =
Sheets("Sheet2").Range("a2:j2000").Value
Sheets("Sheet2").Range("a2:j2000").Clear
ActiveWorkbook.Names("web").Delete
End Sub
Sub MyWait(PauseSeg As Double)
' Orlando Magalhães Filho
Dim Start
Start = Timer
Do While Timer < Start + PauseSeg
DoEvents
Loop
End Sub
(This pulls down about 2000 rows of data by 10 columns wide and places
it starting in column K/row 2.


I have a column that checks for duplicates, like so, because the data
they supply me with always has duplicates.:
=IF(MAX(COUNTIF(range1,range1))>1,"Duplicates","No Duplicates")


I have either formulas or data in the first 10 columns of my
spreadsheet that get populated when I pull down a new version of the
internet spreadsheet.


All I have to do is capitalize the product names, spellcheck the
product names, and remove the double-quotes from the product names
because they don't convert 'nicely' when I convert the entire
spreadsheet to a TAB file.


There ya' go.


I've been given a lot of help by a lot of different people in this
forum. I would not have been able to accomplish this without their
help. I want to thank everyone who has helped me with this.


Barb