• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

convert array to rows and match

SSEK

SSEK

New Member
Hi,
This is a first on excel forum..
Please help me with making this easy, currently i am doing this action and saving it as macro.

1. Automate all data into single column, data in rows as in 'score sheet' see Z column
2. They need to match to data in 'final sheet', see E, F columns
3. Automate data flow from multiple files that have score sheets into one final file with "final sheet" format

Thanks in advance to all the Excel Gurus :)

Best regards,
 

Attachments

  • Convert array to row-example.xlsx
    19.6 KB · Views: 9
Hi David,
Data in final sheet is independent, coming from machine. Data from score is manual scoring. i hope this is clear.
 
Hi SSEK ,

First, English is not my native language,
Secondly, if you can somehow explain more thoroughly, I'd be happy to help.

David
 
@bines53 Not at all, as I said your English is good, not as you implied with saying it was not your native language.
Many on this forum who use English as a second (or third) language have a better understanding of English then they realise, English grammar and syntax is difficult for those who are born to the language.
 
Hi SSEK ,

Maybe it's what you're looking for ,
=SUMPRODUCT(SIGN(MATCH(E2:E25,Score!Z2:Z25,0))),put in final sheet.

David
 
Hi guys, Also English is not my first language. No offense and I agree with bobhc, don't loose sleep over language- if you can ask and keep asking - we will all find answers, at least that's my belief.
Coming to the excel questions-
1. Data in "FINAL" Sheet is coming from machine Columns A-D
2. Data in "SCORE"sheet is manual scoresheet.
Actions/Automatic excel magic I want
1. Copying data into "FINAL" from "SCORE", and matching position in column E
2. COpying data "1, 2, 3, 4, B etc" "SCORE" and having it in column F

Then I need to automate this for
1. 20 IDs for each "score" sheet
2. 100 files with this kind of data format with "SCORE" & "FINAL"
3. Ultimately I will need to copy this into one excel file before going for statistical analyses.

I hope this is clear, please do not hesitate to ask questions, I appreciate the time and help. Best regards,
 
David,
Thanks,Yes, I agree this works. But should I type the formula and Ctrl+Shift+Enter. I was not able to see the formula do the output.
Can you explain? Thank you.

1. In"SCORE" sheet -there are multiple such array of 10x12 (120 cells).
2. In "FINAL" sheet, data from array need to go directly to the "FINAL" sheet in column F ?
Also has to MATCH with
ID (Column A)
Position (Column C)

3. How do I make a macro to move data from "SCORE" sheet to "FINAL" sheet in each excel file.
4. How do I make a macro to move data from multiple files with "SCORE & FINAL sheet into one excel file for stat work?
Thanks for your tips, I hope I have explained ok.
 
Hi SSEK ,

Move forward a little bit,
I filled the column AA, 120 lines, I understand that you have these four, also made a match between column AA SCORE sheet, to column F,FINAL sheet.
=SUMPRODUCT(MATCH(F2:F25,Score!AA2:AA121,0))

So far it's all right ?

David
 
Hi SSEK ,

Look in the file, which matches exactly do you want?
That can do a lot of combinations.

David
 

Attachments

  • Convert array to row-example-1.xlsx
    33.7 KB · Views: 9
You must log in or register to reply here.
Back
Top