PLS, I need help, it is hard

A

Attie

I receive this doc (below), different names each time. And i need to have the
top one("mo" or whatever (there are no entries next to these)) to be shown
with the sum of the totals below . Then from "name"down to the totals i have
to have it hidden, now here is where it becomes tricky thay are not always
the same size so i can't spesify a certain cell. There are about 30 of these
"mo"'s or whatever. Can someone help

Mo
Name Perc Ovd Exp Tot for Prd
ag 2 4
ng 3 444
ngh 4 446
9 894

gp
Name Perc Ovd Exp Tot for Prd
af 56 363
sdh 242 6
298 369

Need to be shown as:
Mo 9 894
gp 298 369

Thanks
 
T

Toppers

Assuming your data in on Sheet1 starting row 1:

On Sheet2:

in A2:


=IF(ISERROR(SMALL(IF(Sheet1!$A$1:$A$100="Name",ROW(Sheet1!$A$1:$A$100),""),ROW($A1))),"",INDEX(Sheet1!A$1:A$100,N(SMALL(IF(Sheet1!$A$1:$A$100="Name",ROW(Sheet1!$A$1:$A$100)-1,""),ROW($A1)))))

Enter with Ctrl+Shift+Enter - you will {} brackets round the formula - and
copy down until you get a blank cell.

in B2:

=IF(ISERROR(SMALL(IF(--(Sheet1!$A$1:$A$100="")*ISNUMBER(Sheet1!$B$1:$B$100),ROW(Sheet1!$A$1:$A$100),""),ROW($A1))),"",INDEX(Sheet1!B$1:B$100,N(SMALL(IF(--(Sheet1!$A$1:$A$100="")*ISNUMBER(Sheet1!$B$1:$B$100),ROW(Sheet1!$A$1:$A$100),""),ROW($A1)))))

Again enter with Ctrl+Shift+Enter.

Copy across to C and down until you get a blank.

Change $A$1:$A$100 (and B & C) as needed.

HTH
 
H

Harlan Grove

Toppers said:
Assuming your data in on Sheet1 starting row 1:

On Sheet2:

in A2:

=IF(ISERROR(SMALL(IF(Sheet1!$A$1:$A$100="Name",
ROW(Sheet1!$A$1:$A$100),""),ROW($A1))),"",INDEX(Sheet1!A$1:A$100,
N(SMALL(IF(Sheet1!$A$1:$A$100="Name",ROW(Sheet1!$A$1:$A$100)-1,""),
ROW($A1)))))
Click to expand...
....

You're fetching the topmost nonblank string in Sheet1!A1:A100. Don't
overcomplicate it.

A2:
=VLOOKUP("?*",Sheet1!$A$1:$A$100,1,0)

The next name is a little more complicated.

A3 [array formula]:
=INDEX(Sheet1!$A$1:$A$100,MATCH(TRUE,ISBLANK(INDEX(Sheet1!$A$1:$A$100,
MATCH(A2,Sheet1!$A$1:$A$100,0)+1):Sheet1!$A$100),0)
+MATCH(A2,Sheet1!$A$1:$A$100,0)+2)

in B2:

=IF(ISERROR(SMALL(IF(--(Sheet1!$A$1:$A$100="")
*ISNUMBER(Sheet1!$B$1:$B$100),ROW(Sheet1!$A$1:$A$100),""),
ROW($A1))),"",INDEX(Sheet1!B$1:B$100,N(SMALL(IF(--(Sheet1!$A$1:$A$100="")
*ISNUMBER(Sheet1!$B$1:$B$100),ROW(Sheet1!$A$1:$A$100),""),ROW($A1)))))
Click to expand...
....

No need for the rather expensive SMALL calls.

B2 [array formula]:
=INDEX(Sheet1!$A$1:$C$100,MATCH(TRUE,ISBLANK(INDEX(Sheet1!$A$1:$A$100,
MATCH($A2,Sheet1!$A$1:$A$100,0)+1):Sheet1!$A$100),0)
+MATCH($A2,Sheet1!$A$1:$A$100,0),COLUMNS($A2:B2))

Fill B2 right into C2. Fill B2:C2 down into B3:C3. Fill A3:C3 down as far as
needed.
 
N

Neil

Attie said:
I receive this doc (below), different names each time. And i need to have
the
top one("mo" or whatever (there are no entries next to these)) to be shown
with the sum of the totals below . Then from "name"down to the totals i
have
to have it hidden, now here is where it becomes tricky thay are not always
the same size so i can't spesify a certain cell. There are about 30 of
these
"mo"'s or whatever. Can someone help

Mo
Name Perc Ovd Exp Tot for Prd
ag 2 4
ng 3 444
ngh 4 446
9 894

gp
Name Perc Ovd Exp Tot for Prd
af 56 363
sdh 242 6
298 369

Need to be shown as:
Mo 9 894
gp 298 369

Thanks
Click to expand...
I've tried to reply, but the little exe file that was attached might be
masking things...
sorry if there are three of my replies - I can't see the first two.
Let me know if a little program would help to read your input doc and create
a csv file formatted as you want.
- and an email addy I can send it to ... ready written, just can't seem to
attach it.
cheers,
Neil
 
P

Peo Sjoblom

You should probably avoid posting exe attachments, nobody in this day and
age in their right mind would open it.
 
N

Neil

Peo Sjoblom said:
You should probably avoid posting exe attachments, nobody in this day and
age in their right mind would open it.
Regards,
Peo Sjoblom
Click to expand...
True, but the email address from attie didn't seem to accept things either,
so I thought a post here would be OK.
But wouldn't doing a virus/trojan check make it safe enough ?
.... maybe I'm just too naive/innocent...
Oh well, I'll stick to text in future. Thanks for the advice.
cheers,
Neil
 
Top