-1

worksheet showing data

How can I extract all the records stacked into a single column and rearrange into a 'normal' table with records in rows and fields in columns? Data is "vertical" because that was arrangement in .DOC file from which I extracted the info.

I have many records of data that are stacked into (actually) two columns. Col. A contains field name, Col B contains the field value. Some of the records don't have all the fields, i.e., rows 2,3,4,5 contain values for 1st record; rows 6,7,8 values for 2nd record. Thanks.

Thank you all for quick replies. Apologies that my question was ambiguous.

Improve this question
1
  • 1
    Your question isn't clear now, could you please post some sample data? Is there any information stored which links rows to records, or just need to check when list of field names restart? Why do you have transpose tag, do you also want to transpose your data? Why do you have extract tag?
    – Máté Juhász
    Apr 21, 2016 at 8:23

2 Answers 2

Reset to default
0

I assume that your empty values points to upper cell that is not empty. If I correctly understand your data looks like columns below and you want to fill empty cells.

Name    Value
N1      V1
N2  
N3  
N4      V2
N5  
N6  
N7      V3
N8

  1. Select column2 (values) cells

  2. Home > Editing section > Find and replace sub menu > Go to special: that will select all blank lines.

  3. When all blank cells are selected click to formula bar and enter top cell name. In my columns it is B2. Type "=$B2" and press Ctrl + Enter

All blank lines will be replaced with the top cell values. Now you can do whatever you want with your data. If this is not you want then visualize your data to make it clear.

Improve this answer
4
  • 1
    It is great that you are helping answer questions, but this one is so unclear I am not sure how you can know what they are in need of. Please explain how your answer address the question asked: How can I extract all the records stacked into a single column?
    – CharlieRB
    Apr 21, 2016 at 12:48
  • @CharlieRB Agree but the question itself is not clear. Give a chance to my guess. Question owner should make a comment on this.
    – Oktay
    Apr 21, 2016 at 14:01
  • 1
    This community is about accurate answers, not guesses. Here is what it says in the help center topic How do I write a good answer - Not all questions can or should be answered here. Save yourself some frustration and avoid trying to answer questions which...are unclear or lacking specific details that can uniquely identify the problem. Following the site guidelines will help you gain reputation quickly and avoid negative feedback or down votes. All you need to do to improve this answer is to explain how it answers the question.
    – CharlieRB
    Apr 21, 2016 at 14:09
  • @CharlieRB I agree with you and I'll keep your suggestion. Why don't you select "This answer is not useful" option? Also question is not marked as "unclear".
    – Oktay
    Apr 25, 2016 at 9:04
0

  1. Enter the following into I4, then press CtrlShiftEnter to enter it as Array Formula

    =IFERROR(INDEX($F$4:$F$15,MATCH(I$3&$H4,$E$4:$E$15&$B$4:$B$15,0)),"")

  2. Copy I4 to I4:L7

Reference: How to use the INDEX and MATCH worksheet functions with multiple criteria in Excel

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .