(Msg. 1) Posted: Mon Aug 18, 2008 10:01 am
Post subject: Can I do this in Excel 2007? Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
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!
(Msg. 2) Posted: Mon Aug 18, 2008 3:48 pm
Post subject: Re: Can I do this in Excel 2007? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Mon, 18 Aug 2008 10:01:00 -0700, cyndiwise notsowise
<cyndiwisenotsowise.TakeThisOut@discussions.microsoft.com> 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
(Msg. 3) Posted: Mon Aug 18, 2008 6:15 pm
Post subject: Re: Can I do this in Excel 2007? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <cyndiwisenotsowise.TakeThisOut@discussions.microsoft.com> wrote
in message news:346763AB-4C2B-495A-9AB5-CD4F2E6D1E6E@microsoft.com...
> 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
(Msg. 4) Posted: Mon Aug 18, 2008 6:15 pm
Post subject: Re: Can I do this in Excel 2007? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <cyndiwisenotsowise.TakeThisOut@discussions.microsoft.com> wrote
> in message news:346763AB-4C2B-495A-9AB5-CD4F2E6D1E6E@microsoft.com...
> > 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
>
(Msg. 5) Posted: Mon Aug 18, 2008 7:34 pm
Post subject: Re: Can I do this in Excel 2007? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <cyndiwisenotsowise RemoveThis @discussions.microsoft.com> wrote
in message news:EC8EB47D-07D5-464E-B7F4-77F689F8F5C2@microsoft.com...
> 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" <cyndiwisenotsowise RemoveThis @discussions.microsoft.com>
>> wrote
>> in message news:346763AB-4C2B-495A-9AB5-CD4F2E6D1E6E@microsoft.com...
>> > 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
>>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office