Adding names together

[Bhuktar S]

(Mr. Frank Kabel may refine further to my thread "List those which ar
selected")
Column A has names list.
Columns B to M are for entering qty. of items required and the qty. ca
be 1 or 2 or 3 max. for any items in any coulmns B, C, D...
Say,
B1=1, C1=1, D1=0, E1=2,.......
B2=0, C2=2, D2=2, E2=1,.......
B3=1, C3=1, D3=1, E3=2,.......
and so on upto row 40.
Max. 20 itmes can be selected per column.
In column N or in other sheet, I want the list of the names of adde
together but with a gap of 1 row for each column, as
(written in the bracket is for clarifying)
N1="1 no."&A1 (selected by B1)
N2="1 no."&A3 (selected by B3)
N3="" (Gap) (end of selection in B)
N4="1 no."&A1 (selected by C1)
N5="2 nos."&A2 (selected by C2)
N6="1 no."&A3 (selected by C3)
N7=""(Gap) (end of selection in C)
N8="2 nos."&A2 (selection by D2)
N9="1 no."&A3 (selection by D3)
N10=""(Gap) (end of selection in D)
N11="2 nos."&A1 (selected by E1)
N12="1 no."&A2 (selected by E2)
N13="2 nos."&A3 (selected by E3)
....
....
I hope there is a solution

 

[Frank Kabel]

Hi
try the array formula in N1:
=IF(MOD(ROW(),3)=0,"",MOD(ROW(),3) & " no. " &
INDEX('source_sheet'!$A$1:$A$40,SMALL(IF(OFFSET('source_sheet'!$B$1:$B$
40,0,INT((ROW()-1)/3))=1,ROW('source_sheet'!$A$1:$A$40)),ROW(1:1)))
and copy down

 

[Bhuktar S]

Dear Frank,
After entering the formula, excel asked to correct it with addition o
) at the end of the formula. OK. Then result in
N1 = 1 no. A1
N2 = #NUM
N3 = "" (blank)
N4 = #NUM
N5 = #NUM
N6 = "" (blank)
and from N7 downwards recurrence of N4 to N6 above.
I enetered qty. in B1=2, B2=1, C1=1, C3=2 to test.
Conclusion:
- Only the N1 has result
- N1 indicates 1 no. instead of 2 no.
Please advise.
Thanks

 

[Frank Kabel]

Hi
yes, sorry there was one ')' missing. Did you enter this formula with
CTRL+SHIFT+ENTER?

 

[Bhuktar S]

Dear Frank, yes, I entered with ctrl+shift+enter.
I want to repeat the requirement, since, I think what I would get fro
your formula may not be what I want.
Assume A has different kind of furnitures.
B,C,D.. are rooms.
For each room any furniture can be selected (max. 20 types per room
and selected type qty. not >3.
What I need is, list the selected of B, then gap of 2 rows, list th
selected of C, then gap of 2 rows and so on for all rooms.
It means all blanks of B, C , D... are not listed and the list is i
one column (say, N) only, one after the other but 2 gaps of rows fo
each room.
These 2 gaps is just to make the list of each rooms seperated from eac
other (so, the gap can be 1 also).
I would prefer qty. & names in 2 columns (this is my revise
requirment). Say, column N for qty. & O for item name.
Hope I made you understand

 

[Frank Kabel]

Hi
if you like email me your file as I'm now not so sure about your
spreadsheet layout. Please describe in this mail what you're trying to
do. Also show the existing formulas in the file and describe what is
not working
email: frank[doit]kabel[at]freenet[dot]de

 

[Bhuktar S]

I was making a sample file for you, but excel has reported erro
everytime I tried to make it. The formula was put & edited for th
sample. Data was put & suddenly '...error...' mesage by excel
application is shut down. This happened 4 times. May be the formula i
too much burden for excel memory or so. I shall try again later afte
2-3 days. Please keep track of my thread.
Thanks