Converting Table to "Flat File"

[zippyfrog]

Hi - I am new to this forum and not sure what the best way to ask this is. Or even if I am using the right terms. Right now I have a spreadsheet that is acting as a table to represent data. What I want to do is convert the table into rows of data so that I can manipulate it in Access. My data represents student names and then across the top are the 50 test questions. In Excel, I am putting an "x" in each cell whenever a student gets a question wrong. What I would like to do is be able to someone covert the table into a flat file so when I import it into Access I can do more with the data.

Here is what I have setup. On Sheet 1, the top row contains the question number. The first column contains the student name, then whenever there is an "x" that indicates a wrong question. This is on the first tab, which I called data. Then, on the second tab, which I called listing, is what I would like to have the results be. The student name and then next to the name the question they got wrong. One line per question.

I tried searching online but I am not sure if I am even describing this correctly...

Any help you could give would be appreciated. Thanks in advance!

Sample Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50
2	Chris				x									x																		x									x									x
3	Jon		x								x										x											x												x							x
4	Sally		x												x											x													x									x
Data

Sample Data.xlsx
	A	B
1	Chris	4
2	Chris	13
3	Chris	31
4	Chris	40
5	Chris	49
6	Jon	2
7	Jon	10
8	Jon	20
9	Jon	31
10	Jon	45
11	Jon	50
12	Sally	2
13	Sally	14
14	Sally	25
15	Sally	38
16	Sally	47
Listing

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub zipyfrog()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   With Sheets("Data")
      Ary = .Range("A1:AY" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 2)
   
   For r = 2 To UBound(Ary)
      For c = 2 To UBound(Ary, 2)
         If Ary(r, c) = "x" Then
            nr = nr + 1
            Nary(nr, 1) = Ary(r, 1)
            Nary(nr, 2) = Ary(1, c)
         End If
      Next c
   Next r
   Sheets("Listing").Range("A2").Resize(nr, 2).Value = Nary
End Sub

 

[zippyfrog]

That is exactly what I needed! Thanks! I couldn't find that anywhere online.

 

[Fluff]

You're welcome & thanks for the feedback.