Help with excel formula

[kevvukeka]

Hi All,

I need some help with an excel formula. Below is my scenario:



Invoice Number
Claim Number
xxxxxx
xxxxxx


A
1




A
2




A
3




B
4




B
5




B
6




C
7




C
8




C
9






I need the output as below:




Invoice Numbers
Claim1
Claim 2
Claim 3


A
1
2
3


B
4
5
6


C
7
8
9




Can someone kindly suggest something as I haven't ever used array formulas.


Thanks a lot for your help.

Kindly assume the first table represents an excel sheet for the convenience of cell references.

[Bob Phillips]

Add a column headed Claim ID, and add this formula to the first row and copy down

="Claim " &COUNTIF($A$2:$A2,A2)

Then pivot the data.

[kevvukeka]

Hi XLD,

thanks for the reply. I actually tried to provide a simpler view of my problem. May be it actually showed my requirement in a different way. I apologize for that. Below is my actual scenario.



Invoice Number
Claim Number
place of service
country


20140502_A
334872




20140502_A
357178




20140502_A
357576




697986_B
352584




697986_B
356921




697986_B
356869




FAB61024
357138




FAB61024
356841




FAB61024
356722






The output I want is as below:



Invoice Number
Claims
Claims
Claims


20140502_A
334872
357178
357576


697986_B
352584
356921
356869


FAB61024
357138
356841
356722





Thanks for your time!!!

[Bob Phillips]

Same response.

[kevvukeka]

u r a genius XLD :bow:....I realized what I was doing wrong..

Thanks a lot...