I have some data in Excel that I want to use in SAS Enterprise Miner, but I need to change the Excel data into something that SAS EM can understand. I've attached a picture of what I'm trying to do and the Excel file with my data. I've tried using vlookup and index but really have no idea how to do this. Any help would be greatly appreciated!
Hello and Welcome to the forum
in L2 copy paste below
=SUM($C$2:K2)
in M1 type Customer Number and In M2 copy paste Below then hold control and shift together and hit enter to make it array formula
=INDEX($A$2:$A$4999,MIN(IF(ROW(A1)<=$L$2:$L$4999,ROW($L$2:$L$4999)-1)))
in N1 type Listing and In N2 copy paste Below then hold control and shift together and hit enter to make it array formula
=INDEX($C$1:$K$1,SMALL(IF(INDEX($C$2:$K$5000,MAX(IF($A$2:$A$5000=M2,ROW($A$2:$A$5000)-1)),0)=1,COLUMN($C$1:$K$1)-2),COUNTIF($M$2:M2,M2)))
Then drag both the formula down.
You need to drag the formula down up to 14655 rows. In attached file it is only upto 5000 rows.
Last edited by hemesh; 04-27-2014 at 01:45 PM.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
THANK YOU SO MUCH HEMESH! Your response was well explained and answered my question fully. I can't explain how grateful I am for your help - thank you thank you thank you!
You are welcome and I am glad I could help you.
You can say thank by clicking add rep icon in the bottom left corner of my post.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks