Can I do this in Excel 2007?

[cyndiwise notsowise]

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

 

[Roger Govier]

Hi Cyndi

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

Now, Mark this new column>Data>Filter>Advanced Filter>Unique values
only>Extract to new location> choose a further location on your sheet in a
blank column.
Data>Filter>Show 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 paste>Special>Values to your new
location.

 

[cyndiwise notsowise]

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]

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 data>paste Special>Values 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.

 

[Ron Rosenfeld]

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