Can I do this in Excel 2007?
 

I need to create a list in a new spreadsheet of unique values by pulling the
data from another spreadsheet in Excel 2007.

The value I want to check against is a 6-digit number (SKU) in a column on
the existing spreadsheet. Most of the SKUs are unique. However, many of the
first 5 digits in the SKU are repeated in the column. What I need to do on
the new spreadsheet is have a column of unknown rows that contain only ONE
instance of the first 5 digits in the SKUs on the old spreadsheet.

Example:

Old spreadsheet, Column A:
130241
130242
130243
130244
130245
131241

New spreadsheet, Column A:
13024
13124

I hope this makes sense! I will also need to have a column on the new
spreadsheet that counts the number of occurences for the first 5 digits of
the SKU. But, I think that will just be a COUNTIF function. Using the above
example, the final result on the new spreadsheet would be:

ColA | ColB |
13024 | 5 |
13124 | 1 |


I just can't wrap my head around how to get Excel to look up the first
5-digits in a column, then only list it once on the new spreadsheet, even
though there may be several instances in the old spreadsheet. The purpose of
this is so I can copy and paste the values in the new spreadsheet as a new
..csv file for uploading to my database.

I hope someone can help me with this, otherwise I will be manually counting
through thousands of SKUs!

Thanks,
Cyndi

Can I do this in Excel 2007?
 

Hi Cyndi

You could use a helper column on Spreadsheet A
In the helper column, enter
=Left(A1,5)

Now, Mark this new columnDataFilterAdvanced FilterUnique values
onlyExtract to new location choose a further location on your sheet in a
blank column.
DataFilterShow All
You will now have a list of unique values in this latest column you have
used.
Alongside this enter
=COUNTIF(A:A,X1) where X1 represents the cell reference of the start of
your list.
Copy down

Copy this block of 2 columns of data and pasteSpecialValues to your new
location.
--
Regards
Roger Govier

"cyndiwise notsowise" wrote
in message ...
I need to create a list in a new spreadsheet of unique values by pulling
the
data from another spreadsheet in Excel 2007.

The value I want to check against is a 6-digit number (SKU) in a column on
the existing spreadsheet. Most of the SKUs are unique. However, many of
the
first 5 digits in the SKU are repeated in the column. What I need to do on
the new spreadsheet is have a column of unknown rows that contain only ONE
instance of the first 5 digits in the SKUs on the old spreadsheet.

Example:

Old spreadsheet, Column A:
130241
130242
130243
130244
130245
131241

New spreadsheet, Column A:
13024
13124

I hope this makes sense! I will also need to have a column on the new
spreadsheet that counts the number of occurences for the first 5 digits of
the SKU. But, I think that will just be a COUNTIF function. Using the
above
example, the final result on the new spreadsheet would be:

ColA | ColB |
13024 | 5 |
13124 | 1 |


I just can't wrap my head around how to get Excel to look up the first
5-digits in a column, then only list it once on the new spreadsheet, even
though there may be several instances in the old spreadsheet. The purpose
of
this is so I can copy and paste the values in the new spreadsheet as a new
.csv file for uploading to my database.

I hope someone can help me with this, otherwise I will be manually
counting
through thousands of SKUs!

Thanks,
Cyndi

Can I do this in Excel 2007?
 

Hi, Roger:

The reason I wanted to do all of this from the new spreadsheet is that I
have way too many columns on the old spreadsheet as it is! This spreadsheet
is my inventory tracking system, and I have columns for every little detail
you can imagine, plus most of the columns have formulas that pull data from
other spreadsheets (like databases). I really don't want any more columns in
this spreadsheet - the last column is currently EJ - LOL!!

I was thinking I could use the LEFT function as part of the "unique values"
formula in the new spreadsheet. I've used it in the original spreadsheet
nested in an IF function. Do you think this would be possible?

Cyndi

"Roger Govier" wrote:

Hi Cyndi

You could use a helper column on Spreadsheet A
In the helper column, enter
=Left(A1,5)

Now, Mark this new columnDataFilterAdvanced FilterUnique values
onlyExtract to new location choose a further location on your sheet in a
blank column.
DataFilterShow All
You will now have a list of unique values in this latest column you have
used.
Alongside this enter
=COUNTIF(A:A,X1) where X1 represents the cell reference of the start of
your list.
Copy down

Copy this block of 2 columns of data and pasteSpecialValues to your new
location.
--
Regards
Roger Govier

"cyndiwise notsowise" wrote
in message ...
I need to create a list in a new spreadsheet of unique values by pulling
the
data from another spreadsheet in Excel 2007.

The value I want to check against is a 6-digit number (SKU) in a column on
the existing spreadsheet. Most of the SKUs are unique. However, many of
the
first 5 digits in the SKU are repeated in the column. What I need to do on
the new spreadsheet is have a column of unknown rows that contain only ONE
instance of the first 5 digits in the SKUs on the old spreadsheet.

Example:

Old spreadsheet, Column A:
130241
130242
130243
130244
130245
131241

New spreadsheet, Column A:
13024
13124

I hope this makes sense! I will also need to have a column on the new
spreadsheet that counts the number of occurences for the first 5 digits of
the SKU. But, I think that will just be a COUNTIF function. Using the
above
example, the final result on the new spreadsheet would be:

ColA | ColB |
13024 | 5 |
13124 | 1 |


I just can't wrap my head around how to get Excel to look up the first
5-digits in a column, then only list it once on the new spreadsheet, even
though there may be several instances in the old spreadsheet. The purpose
of
this is so I can copy and paste the values in the new spreadsheet as a new
.csv file for uploading to my database.

I hope someone can help me with this, otherwise I will be manually
counting
through thousands of SKUs!

Thanks,
Cyndi

Can I do this in Excel 2007?
 

Hi Cyndi

EJ still leaves a lot of columns to go !!!
Anyway, you could in the new Workbook use
=LEFT('[myoldworkbook.xls]Sheet1'!A1,5)
to pull the values across from the old workbook to the new.
Copy he column of datapaste SpecialValues back over itself to "fix" the
data.
Then use the Advanced filter to create a unique list in the new workbook.

use
=COUNTIF('[myoldworkbook.xls]Sheet1'!A:A,X1)
again, where X1 represents the first cell in your range of unique values.

--
Regards
Roger Govier

"cyndiwise notsowise" wrote
in message ...
Hi, Roger:

The reason I wanted to do all of this from the new spreadsheet is that I
have way too many columns on the old spreadsheet as it is! This
spreadsheet
is my inventory tracking system, and I have columns for every little
detail
you can imagine, plus most of the columns have formulas that pull data
from
other spreadsheets (like databases). I really don't want any more columns
in
this spreadsheet - the last column is currently EJ - LOL!!

I was thinking I could use the LEFT function as part of the "unique
values"
formula in the new spreadsheet. I've used it in the original spreadsheet
nested in an IF function. Do you think this would be possible?

Cyndi

"Roger Govier" wrote:

Hi Cyndi

You could use a helper column on Spreadsheet A
In the helper column, enter
=Left(A1,5)

Now, Mark this new columnDataFilterAdvanced FilterUnique values
onlyExtract to new location choose a further location on your sheet in
a
blank column.
DataFilterShow All
You will now have a list of unique values in this latest column you have
used.
Alongside this enter
=COUNTIF(A:A,X1) where X1 represents the cell reference of the start of
your list.
Copy down

Copy this block of 2 columns of data and pasteSpecialValues to your new
location.
--
Regards
Roger Govier

"cyndiwise notsowise"
wrote
in message ...
I need to create a list in a new spreadsheet of unique values by
pulling
the
data from another spreadsheet in Excel 2007.

The value I want to check against is a 6-digit number (SKU) in a column
on
the existing spreadsheet. Most of the SKUs are unique. However, many of
the
first 5 digits in the SKU are repeated in the column. What I need to do
on
the new spreadsheet is have a column of unknown rows that contain only
ONE
instance of the first 5 digits in the SKUs on the old spreadsheet.

Example:

Old spreadsheet, Column A:
130241
130242
130243
130244
130245
131241

New spreadsheet, Column A:
13024
13124

I hope this makes sense! I will also need to have a column on the new
spreadsheet that counts the number of occurences for the first 5 digits
of
the SKU. But, I think that will just be a COUNTIF function. Using the
above
example, the final result on the new spreadsheet would be:

ColA | ColB |
13024 | 5 |
13124 | 1 |


I just can't wrap my head around how to get Excel to look up the first
5-digits in a column, then only list it once on the new spreadsheet,
even
though there may be several instances in the old spreadsheet. The
purpose
of
this is so I can copy and paste the values in the new spreadsheet as a
new
.csv file for uploading to my database.

I hope someone can help me with this, otherwise I will be manually
counting
through thousands of SKUs!

Thanks,
Cyndi

Can I do this in Excel 2007?
 

On Mon, 18 Aug 2008 10:01:00 -0700, cyndiwise notsowise
wrote:

I need to create a list in a new spreadsheet of unique values by pulling the
data from another spreadsheet in Excel 2007.

The value I want to check against is a 6-digit number (SKU) in a column on
the existing spreadsheet. Most of the SKUs are unique. However, many of the
first 5 digits in the SKU are repeated in the column. What I need to do on
the new spreadsheet is have a column of unknown rows that contain only ONE
instance of the first 5 digits in the SKUs on the old spreadsheet.

Example:

Old spreadsheet, Column A:
130241
130242
130243
130244
130245
131241

New spreadsheet, Column A:
13024
13124

I hope this makes sense! I will also need to have a column on the new
spreadsheet that counts the number of occurences for the first 5 digits of
the SKU. But, I think that will just be a COUNTIF function. Using the above
example, the final result on the new spreadsheet would be:

ColA | ColB |
13024 | 5 |
13124 | 1 |


I just can't wrap my head around how to get Excel to look up the first
5-digits in a column, then only list it once on the new spreadsheet, even
though there may be several instances in the old spreadsheet. The purpose of
this is so I can copy and paste the values in the new spreadsheet as a new
.csv file for uploading to my database.

I hope someone can help me with this, otherwise I will be manually counting
through thousands of SKUs!

Thanks,
Cyndi

Can you use VBA for this task?
--ron