 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Right, I have a pivot table.
This shows supplier name and order numbers
Supplier name in column A
Supplier order numbers in column B
Supplier name only shown once
How can I get Supplier name to repeat?
This is so I can do a simple count of the number of orders - my raw data has the order lines (more than the number of orders) but I only want the number of orders themselves.....and don't want to have to copy and paste the supplier name...
If count of orders is all you want base pivot table on these only
Row labels = Supplier
Values = Count of order numbers
or is there more to it?
Yes, but my dataset has
Supplier Name
Supplier PO number
Supplier PO line number - of which there are many against same PO number
So, if I do count of order numbers, it is returning count of order lines.
Don't include line numbers in the datasource
Datasource = $A$1 to $B$1000 (or wherever it ends)
A B
ABC 1234
ABC 1234
ABC 1234
ABC 1235
ABC 1235
ABC 1235
XYZ 1240
XYZ 1241
Total would be ABC = 2 & XYZ = 2
Put order number in column A?
or
Copy the 2 fields you want into a new sheet and use the "Remove Duplicates" function.
Use =Left / =Right / =Mid / =len to split it up if the order number and line number are in the same field.
I'd go for TSY's Remove Duplicates.
If you need to do it regularly, record a quick macro. I think recording Ctrl-A for select current table won't work properly, so read this (point 19):
[url= http://support.microsoft.com/kb/291308 ]http://support.microsoft.com/kb/291308[/url]
-Edit - repeating an item to fill blanks as per your original suggestion is a surprisingly large pain in the arse. If you can avoid it, do. Been there, done that, got the t-shirt...