Excel, How to extract records that are all in one column

-1

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.

user1704475

Posted 2016-04-21T00:24:19.933

Reputation: 1

1Your 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 – 2016-04-21T08:23:43.293

Answers

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

Select column2 (values) cells
Home > Editing section > Find and replace sub menu > Go to special: that will select all blank lines.
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.

Oktay

Posted 2016-04-21T00:24:19.933

Reputation: 1

1It 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 – 2016-04-21T12:48:40.677

@CharlieRB Agree but the question itself is not clear. Give a chance to my guess. Question owner should make a comment on this. – Oktay – 2016-04-21T14:01:50.723

This community is about accurate answers, not guesses. Here is what it says in the [help] 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 – 2016-04-21T14:09:21.483

@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 – 2016-04-25T09:04:42.477

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)),"")
```
Copy I4 to I4:L7

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

wilson

Posted 2016-04-21T00:24:19.933

Reputation: 4 113