Hi, I have two columns consisting of string or nothing. I would like to move all strings into a new column and skip blank cells.
EXAMPLE.jpg
Hi, I have two columns consisting of string or nothing. I would like to move all strings into a new column and skip blank cells.
EXAMPLE.jpg
Last edited by regresss; 05-29-2015 at 02:57 PM.
This type of process is significantly less complicated with code than whatever array function that could do it.
Please Login or Register to view this content.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Okay, that was hard.
Please Login or Register to view this content.
Last edited by daffodil11; 05-29-2015 at 04:15 PM.
Very impressive! Thank you! I have to test it on big data (10k-30k rows and the response time), but so far so good!
Here's another one...
Data Range
A B C D 1 A A A 2 B B 3 C D 4 D D F 5 F G 6 G E 7 E A 8 C 9 D 10
This array formula** entered in D1:
=IFERROR(IFERROR(INDEX(A:A,SMALL(IF(A$1:A$7<>"",ROW(A$1:A$7)),ROWS(D$1:D1))),INDEX(B:B,SMALL(IF(B$1:B$4<>"",ROW(B$1:B$4)),ROWS(D$1:D1)-COUNTIF(A$1:A$7,"<>")))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks