Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 6th 04, 06:45 PM
GI
 
Posts: n/a
Default convert excel list to pivot table

Thank you very much.. sorry , it took a while for me to reply. But, it works
with your macro.
I was wondering if you can help me with another problem that I have.
I created those pivot table with the part numbers as the primary column and
and quantity as the second column by summing the quanitty.
Then I created independent column next to it for my own purposes, but when I
refresh the pivot table, it won't refresh those independent column.
Do you know any tricks for it?
Thx

"Bernie Deitrick" wrote:

GI,

How about a macro: Select all the cells and run this

Sub ConvertToText()
Dim myCell As Range

For Each myCell In Selection
myCell.NumberFormat = "@"
myCell.Value = "'" & myCell.Value
Next myCell

End Sub

HTH,
Bernie
MS Excel MVP

"GI" wrote in message
...
Thanks for the help.
those numbers represent part numbers. There are too many mix type

numbers.
After the dot, there are 4 digits and 5 digits. I tried with your

formula,
it works but for pn such as 012345-001, it did not work and also between 4
and 5 digits.
Those numbers are not from external source. It was entered before and
provided it to me. And those numbers are formatted in cells with custom
number format 0#####.

"Dave Peterson" wrote:

I think the next question is how do you know how many places to keep

after the
"decimal" point.

012345.2310 looks like it could have been 012345.231

If you always have a dot in the part number, is it always followed by 4

digits?

=IF(MOD(A1,1)=0,TEXT(A1,"00000"),TEXT(A1,"00000.00 00"))

=====
If you got this list from an external source (a text file???), it might

be
easier to reimport it into excel--but specify Text for that field.



GI wrote:

Thanks for the help.
Is there a way to make the job simpler... Since I have 2000 numbers to
revise then.
I tried using =text(a1,"000000") but this one doesn't capture the part
number 012345.2310
--GI

"Bernie Deitrick" wrote:

GI,

Format the cells as text prior to entering the numbers, or use a

single
quote in front of the number string.

HTH,
Bernie
MS Excel MVP

"GI" wrote in message
...
Hi.
I've been trying to convert a list of mix numbers such as 010456,

123456,
010686-001, 010686-002, 015678-AB and much more to a pivot table.
Everytime
I created the pivot table using those numbers as pivot, EX: 010456
automatically changed to 10456, it doesn't recognize as 6 digits.

I tried
using text, custom 0#####, still can't get it sort right.
Does anybody know how to solve it. Thanks a lot.





--

Dave Peterson





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
create space in line chart between points, linked to pivot table Mike -Z- Charts and Charting in Excel 1 December 7th 04 09:39 PM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 03:56 AM
How to convert the table in word to excel sheet? Santhosh Excel Discussion (Misc queries) 1 December 2nd 04 10:15 AM
In excel and a pivot table - how can I stop it displaying (blank). Mansel Excel Discussion (Misc queries) 1 December 2nd 04 12:55 AM
using the PIVOT TABLE and CHART Big Red Excel Discussion (Misc queries) 1 November 29th 04 08:42 PM


All times are GMT +1. The time now is 10:00 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017