Hello, I need to extract data form a column based on value of a cell, column "B" has an "item Code", i want to get that item code and insert that into column "A" as shown on sample worksheet, now, if i can also add a blank row above each cell containing this value it would be great, if cell on column "B" has "FS" or "R" i want to place it on column "A" as shown.
test data.xlsx
Still using Excel 2010???
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I do NOT understand.
Why are there values in A10-A14?
There is an R in A8. Why does that not count?
What do you expect to see in (a few) rows BELOW the red row?
YOU know what you're looking at. WE don't. You need to explain the issue completely.
last time you helped me with a different issue, I this all if this manually, we added a lot more items since then.
Thank you.
And how does it differ from your LAST thread. I helped you with one 2 threads ago.
replying again, last time, you helped me extracting values based on item number entered on a different worksheet, if i entered "10FS-1173" it would pull contents adjacent to rows containing that value.
Perhaps this will help.
1. Column A is populated using: =IF(OR(MID(B2,3,2)="fs",MID(B2,3,1)="r"),B2,IF(OR(B2="",LEFT(B2,1)="m",LEFT(B2,1)="s"),"",INDEX(A$1:A1,AGGREGATE(14,6,ROW(A$1:A1)/(A$1:A1<>""),1))))
2. A red border is drawn between sections using: =AND($A2<>"",$A3<>"",$A2<>$A3)
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
This is great! thank you very much, now would it be possible, instead if drawing red line, to leave a blank row, this will create separation from "item block" to "item block".
Thanks again!!!
I believe that it would require VBA to insert the blank rows in columns A:E, however using formulas a range with blank rows could be produced in another set of columns such as H:L.
1. Populate cell F1 with the number 1
2. Populate cells F2 and down using: =IF(AND($A1<>"",$A2<>"",$A1<>$A2),SUM(F1,2),SUM(F1,1))
3. Populate columns H:L using: =INDEX(A$2:A$4252,MATCH(ROW(),$F$2:$F$4252,0))
4. Apply the following custom formatting to columns H:L General;;
5. Apply the following conditional formatting to columns H:L =ISNA(H2)
Let us know if you have any questions.
This works! thank you very much!!
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks