Formula Problem

[IJY14]

Hi All,

Sorry for the simple title, I dont know how to best describe my problem and would love it if someone could help me out. I'l try my best to explain.

Untitled.png

I have a table in which orders have been completed (not in specific order). X person is in column 'A' and all the 'ABC' followed by numbers is their completed parts. The 'ABC' alone is if for some reason they are not able to find/complete the part. These are all imported from another sheet which has been concatenated (ABC in the 1st column then the part number in the 2nd column). E.G. Sheet 1/Column A = ABC all the way down, Sheet 1/Column B = Completed part number (left blank if not completed). This info then concatenates into Sheet 3 to form the above information (attachment).

My question is:

Is it possible to just list the completed ones for each person, I have tried to do macro to delete and shift up if only returns 'ABC' but had no luck nor can I just filter out 'blanks' as their not actually blank due to IF formulas (IF(X="","",X).

I was hoping their would be a formula to use preferable to do this so it will look like the following (doesnt return the 'ABC' only:

1.png

Sorry if this is confusing it's hard for me to explain it but any help would be greatly appreciated.

IJY.

[gfranco]

Hello.
I used an array formula to bring the result as per your demonstration.
I also used an auxiliary cell to avoid using the "iferror" function.
Remember that formulas must be terminated with CTRL + SHIFT + ENTER
See if it helps.

Please Login or Register  to view this content.

[Glenn Kennedy]

I do not understand why you want to avoid using IFERROR. You avoid the helper cell and you can have a formula that is easily dragged across and down. use this in D2, array enter and copy across and down.

=IFERROR(INDEX(A:A,SMALL(IF(A$2:A$10<>"ABC",ROW(A$2:A$10)),ROWS(D$2:D2))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[gfranco]

is a matter of preference only.
Both methods bring the same results

[AlKey]

You can also use a regular formula

Please Login or Register  to view this content.

[IJY14]

Thanks, I have tried both and they work on a basic version of the spreadsheet but unfortunately wont be able to fully try it until after the festive period (had to recreate a basic version).

Alkey, yours only seemed to work when I reverted the > to < (not sure if this will be ok).
Gfranco, Unfortunately it didnt like your formula, stating that there was an error with the $C$1 part.

[alansidman]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)