Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate Data From Vertical Format to Horizontal.
I have read some of the other threads regarding things similar to my problem,
but I think mine is a little unique. I have a list of account numbers with corresponding internal codes. That when pulled form my system look like so. The tricky part for me is that the number of internal codes varies it could be 1 it could be 10. Ultimately I would like it to end up as show on the bottom, is this feasible? 24539603 787 24539603 978 15471183 25 20172710 706 20172710 701 15784301 485 15784301 175 24539603 787 978 15471183 25 20172710 706 701 15784301 485 175 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate Data From Vertical Format to Horizontal.
Add a empty column alongside the account #s. Let's say your acct#s are in
cells B2:B1000 and the new empty column is A. In A2 enter the formula =Countif(B$2:B2,B2) and copy it down to A1000 Make sure each of your columns has a header, say Index (for the new formulas), Accounts, and Codes. Now use that range to create a Pivot table with the Index column as your column headers, the Accounts as row labels and then use Min or Max of the Codes for the data value "Patrick L" wrote: I have read some of the other threads regarding things similar to my problem, but I think mine is a little unique. I have a list of account numbers with corresponding internal codes. That when pulled form my system look like so. The tricky part for me is that the number of internal codes varies it could be 1 it could be 10. Ultimately I would like it to end up as show on the bottom, is this feasible? 24539603 787 24539603 978 15471183 25 20172710 706 20172710 701 15784301 485 15784301 175 24539603 787 978 15471183 25 20172710 706 701 15784301 485 175 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate Data From Vertical Format to Horizontal.
Duke you are awsome!
"Duke Carey" wrote: Add a empty column alongside the account #s. Let's say your acct#s are in cells B2:B1000 and the new empty column is A. In A2 enter the formula =Countif(B$2:B2,B2) and copy it down to A1000 Make sure each of your columns has a header, say Index (for the new formulas), Accounts, and Codes. Now use that range to create a Pivot table with the Index column as your column headers, the Accounts as row labels and then use Min or Max of the Codes for the data value "Patrick L" wrote: I have read some of the other threads regarding things similar to my problem, but I think mine is a little unique. I have a list of account numbers with corresponding internal codes. That when pulled form my system look like so. The tricky part for me is that the number of internal codes varies it could be 1 it could be 10. Ultimately I would like it to end up as show on the bottom, is this feasible? 24539603 787 24539603 978 15471183 25 20172710 706 20172710 701 15784301 485 15784301 175 24539603 787 978 15471183 25 20172710 706 701 15784301 485 175 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
moving data from vertical to horizontal possition | Excel Worksheet Functions | |||
Cross Reference Vertical and Horizontal for Data | Excel Worksheet Functions | |||
Need lookup of data both vertical and horizontal | Excel Discussion (Misc queries) | |||
How to make Horizontal data go Vertical | Excel Worksheet Functions | |||
Converting a large vertical mailing list into a horizontal format | New Users to Excel |