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.
--
Regards
Roger Govier
"cyndiwise notsowise" <(E-Mail Removed)> wrote
in message news:EC8EB47D-07D5-464E-B7F4-(E-Mail Removed)...
> 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 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.
>> --
>> Regards
>> Roger Govier
>>
>> "cyndiwise notsowise" <(E-Mail Removed)>
>> wrote
>> in message news:346763AB-4C2B-495A-9AB5-(E-Mail Removed)...
>> > 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
>>
|
Similar Threads
